{"Entry":{"collection":"sql","key":"prepare","name":"PREPARE","aliases":["prepare"],"metadata":{"aliases":["prepare"],"changed_in":["7.4","8.2","9.0"],"changes":[{"from":"7.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":[],"removed":[]},"status":"added","synopsis":null,"to":"7.3"},{"from":"7.3","purpose_changed":true,"renamed":null,"sections":{"added":["parameters","notes"],"changed":["description","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["PREPARE plan_name [ (datatype [, ...] ) ] AS statement"],"removed":["PREPARE plan_name [ (datatype [, ...] ) ] AS query"]},"to":"7.4"},{"from":"7.4","purpose_changed":false,"renamed":null,"sections":{"added":["examples","see_also"],"changed":["description","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"8.0"},{"from":"8.1","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","notes","examples"],"removed":[]},"status":"changed","synopsis":{"added":["PREPARE name [ (datatype [, ...] ) ] AS statement"],"removed":["PREPARE plan_name [ (datatype [, ...] ) ] AS statement"]},"to":"8.2"},{"from":"8.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes"],"removed":[]},"status":"changed","synopsis":null,"to":"8.3"},{"from":"8.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":{"added":["PREPARE name [ ( data_type [, ...] ) ] AS statement"],"removed":["PREPARE name [ ( datatype [, ...] ) ] AS statement"]},"to":"9.0"},{"from":"9.1","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"9.2"},{"from":"9.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"9.3"},{"from":"9.5","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"9.6"},{"from":"10","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"11"},{"from":"11","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes","examples"],"removed":[]},"status":"changed","synopsis":null,"to":"12"},{"from":"13","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes"],"removed":[]},"status":"changed","synopsis":null,"to":"14"},{"from":"14","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"15"}],"content_hash":"c09bfeacb5b5f14385083574f94b4c34648a7910d542fa84d810c2a9f7da1227","editorial":{},"first_version":"7.3","group":"cursor","imported_at":"2026-09-30T17:43:39.279348+08:00","last_version":"20","name":"PREPARE","object":"","position":12006,"present_in":["7.3","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6","10","11","12","13","14","15","16","17","18","19","20"],"purpose":"prepare a statement for execution","purpose_zh":"","related":["deallocate","execute"],"slug":"prepare","source_rev":"a709ab85","synopsis":"PREPARE name [ ( data_type [, ...] ) ] AS statement","verb":"PREPARE"}},"Definition":{"Collection":"sql","Key":"prepare","SourceDatabase":"center","Version":"18","SourceTable":"sqlcmd","SourceKey":"prepare","SourceRevision":"a709ab85","Facts":{"anchor":"SQL-PREPARE","file":"sql-prepare.html","lang":"en","name":"PREPARE","purpose":"prepare a statement for execution","purpose_zh":"","related":["deallocate","execute"],"sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003ePREPARE\u003c/code\u003e creates a prepared statement. A prepared statement is a server-side object that can be used to optimize performance. When the \u003ccode class=\"command\"\u003ePREPARE\u003c/code\u003e statement is executed, the specified statement is parsed, analyzed, and rewritten. When an \u003ccode class=\"command\"\u003eEXECUTE\u003c/code\u003e command is subsequently issued, the prepared statement is planned and executed. This division of labor avoids repetitive parse analysis work, while allowing the execution plan to depend on the specific parameter values supplied.\u003c/p\u003e\u003cp\u003ePrepared statements can take parameters: values that are substituted into the statement when it is executed. When creating the prepared statement, refer to parameters by position, using \u003ccode class=\"literal\"\u003e$1\u003c/code\u003e, \u003ccode class=\"literal\"\u003e$2\u003c/code\u003e, etc. A corresponding list of parameter data types can optionally be specified. When a parameter's data type is not specified or is declared as \u003ccode class=\"literal\"\u003eunknown\u003c/code\u003e, the type is inferred from the context in which the parameter is first referenced (if possible). When executing the statement, specify the actual values for these parameters in the \u003ccode class=\"command\"\u003eEXECUTE\u003c/code\u003e statement. Refer to \u003ca href=\"/docs/18/sql-execute.html\" title=\"EXECUTE\"\u003e\u003cspan class=\"refentrytitle\"\u003eEXECUTE\u003c/span\u003e\u003c/a\u003e for more information about that.\u003c/p\u003e\u003cp\u003ePrepared statements only last for the duration of the current database session. When the session ends, the prepared statement is forgotten, so it must be recreated before being used again. This also means that a single prepared statement cannot be used by multiple simultaneous database clients; however, each client can create their own prepared statement to use. Prepared statements can be manually cleaned up using the \u003ca href=\"/docs/18/sql-deallocate.html\" title=\"DEALLOCATE\"\u003e\u003ccode class=\"command\"\u003eDEALLOCATE\u003c/code\u003e\u003c/a\u003e command.\u003c/p\u003e\u003cp\u003ePrepared statements potentially have the largest performance advantage when a single session is being used to execute a large number of similar statements. The performance difference will be particularly significant if the statements are complex to plan or rewrite, e.g., if the query involves a join of many tables or requires the application of several rules. If the statement is relatively simple to plan and rewrite but relatively expensive to execute, the performance advantage of prepared statements will be less noticeable.\u003c/p\u003e","key":"description","title":"Description"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eAn arbitrary name given to this particular prepared statement. It must be unique within a single session and is subsequently used to execute or deallocate a previously prepared statement.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003edata_type\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe data type of a parameter to the prepared statement. If the data type of a particular parameter is unspecified or is specified as \u003ccode class=\"literal\"\u003eunknown\u003c/code\u003e, it will be inferred from the context in which the parameter is first referenced. To refer to the parameters in the prepared statement itself, use \u003ccode class=\"literal\"\u003e$1\u003c/code\u003e, \u003ccode class=\"literal\"\u003e$2\u003c/code\u003e, etc.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003estatement\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eAny \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e, \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e, \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e, \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e, \u003ccode class=\"command\"\u003eMERGE\u003c/code\u003e, or \u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e statement.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"Parameters"},{"html":"\u003cp\u003eA prepared statement can be executed with either a \u003cem class=\"firstterm\"\u003egeneric plan\u003c/em\u003e or a \u003cem class=\"firstterm\"\u003ecustom plan\u003c/em\u003e. A generic plan is the same across all executions, while a custom plan is generated for a specific execution using the parameter values given in that call. Use of a generic plan avoids planning overhead, but in some situations a custom plan will be much more efficient to execute because the planner can make use of knowledge of the parameter values. (Of course, if the prepared statement has no parameters, then this is moot and a generic plan is always used.)\u003c/p\u003e\u003cp\u003eBy default (that is, when \u003ca href=\"/docs/18/runtime-config-query.html#GUC-PLAN-CACHE-MODE\"\u003eplan_cache_mode\u003c/a\u003e is set to \u003ccode class=\"literal\"\u003eauto\u003c/code\u003e), the server will automatically choose whether to use a generic or custom plan for a prepared statement that has parameters. The current rule for this is that the first five executions are done with custom plans and the average estimated cost of those plans is calculated. Then a generic plan is created and its estimated cost is compared to the average custom-plan cost. Subsequent executions use the generic plan if its cost is not so much higher than the average custom-plan cost as to make repeated replanning seem preferable.\u003c/p\u003e\u003cp\u003eThis heuristic can be overridden, forcing the server to use either generic or custom plans, by setting \u003ccode class=\"varname\"\u003eplan_cache_mode\u003c/code\u003e to \u003ccode class=\"literal\"\u003eforce_generic_plan\u003c/code\u003e or \u003ccode class=\"literal\"\u003eforce_custom_plan\u003c/code\u003e respectively. This setting is primarily useful if the generic plan's cost estimate is badly off for some reason, allowing it to be chosen even though its actual cost is much more than that of a custom plan.\u003c/p\u003e\u003cp\u003eTo examine the query plan \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e is using for a prepared statement, use \u003ca href=\"/docs/18/sql-explain.html\" title=\"EXPLAIN\"\u003e\u003ccode class=\"command\"\u003eEXPLAIN\u003c/code\u003e\u003c/a\u003e, for example\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eEXPLAIN EXECUTE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e(\u003cem class=\"replaceable\"\u003e\u003ccode\u003eparameter_values\u003c/code\u003e\u003c/em\u003e);\n\u003c/pre\u003e\u003cp\u003eIf a generic plan is in use, it will contain parameter symbols \u003ccode class=\"literal\"\u003e$\u003cem class=\"replaceable\"\u003e\u003ccode\u003en\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e, while a custom plan will have the supplied parameter values substituted into it.\u003c/p\u003e\u003cp\u003eFor more information on query planning and the statistics collected by \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e for that purpose, see the \u003ca href=\"/docs/18/sql-analyze.html\" title=\"ANALYZE\"\u003e\u003cspan class=\"refentrytitle\"\u003eANALYZE\u003c/span\u003e\u003c/a\u003e documentation.\u003c/p\u003e\u003cp\u003eAlthough the main point of a prepared statement is to avoid repeated parse analysis and planning of the statement, \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e will force re-analysis and re-planning of the statement before using it whenever database objects used in the statement have undergone definitional (DDL) changes or their planner statistics have been updated since the previous use of the prepared statement. Also, if the value of \u003ca href=\"/docs/18/runtime-config-client.html#GUC-SEARCH-PATH\"\u003esearch_path\u003c/a\u003e changes from one use to the next, the statement will be re-parsed using the new \u003ccode class=\"varname\"\u003esearch_path\u003c/code\u003e. (This latter behavior is new as of \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 9.3.) These rules make use of a prepared statement semantically almost equivalent to re-submitting the same query text over and over, but with a performance benefit if no object definitions are changed, especially if the best plan remains the same across uses. An example of a case where the semantic equivalence is not perfect is that if the statement refers to a table by an unqualified name, and then a new table of the same name is created in a schema appearing earlier in the \u003ccode class=\"varname\"\u003esearch_path\u003c/code\u003e, no automatic re-parse will occur since no object used in the statement changed. However, if some other change forces a re-parse, the new table will be referenced in subsequent uses.\u003c/p\u003e\u003cp\u003eYou can see all prepared statements available in the session by querying the \u003ca href=\"/docs/18/view-pg-prepared-statements.html\" title=\"53.16. pg_prepared_statements\"\u003e\u003ccode class=\"structname\"\u003epg_prepared_statements\u003c/code\u003e\u003c/a\u003e system view.\u003c/p\u003e","key":"notes","title":"Notes"},{"html":"\u003cp\u003eCreate a prepared statement for an \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e statement, and then execute it:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003ePREPARE fooplan (int, text, bool, numeric) AS\n    INSERT INTO foo VALUES($1, $2, $3, $4);\nEXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);\n\u003c/pre\u003e\u003cp\u003eCreate a prepared statement for a \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e statement, and then execute it:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003ePREPARE usrrptplan (int) AS\n    SELECT * FROM users u, logs l WHERE u.usrid=$1 AND u.usrid=l.usrid\n    AND l.date = $2;\nEXECUTE usrrptplan(1, current_date);\n\u003c/pre\u003e\u003cp\u003eIn this example, the data type of the second parameter is not specified, so it is inferred from the context in which \u003ccode class=\"literal\"\u003e$2\u003c/code\u003e is used.\u003c/p\u003e","key":"examples","title":"Examples"},{"html":"\u003cp\u003eThe SQL standard includes a \u003ccode class=\"command\"\u003ePREPARE\u003c/code\u003e statement, but it is only for use in embedded SQL. This version of the \u003ccode class=\"command\"\u003ePREPARE\u003c/code\u003e statement also uses a somewhat different syntax.\u003c/p\u003e","key":"compatibility","title":"Compatibility"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/deallocate/?v=18\" title=\"DEALLOCATE\"\u003e\u003cspan class=\"refentrytitle\"\u003eDEALLOCATE\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/execute/?v=18\" title=\"EXECUTE\"\u003e\u003cspan class=\"refentrytitle\"\u003eEXECUTE\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"See Also"}],"sections_same_as":"","slug":"18","synopsis_html":"PREPARE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e [ ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003edata_type\u003c/code\u003e\u003c/em\u003e [, ...] ) ] AS \u003cem class=\"replaceable\"\u003e\u003ccode\u003estatement\u003c/code\u003e\u003c/em\u003e","synopsis_text":"PREPARE name [ ( data_type [, ...] ) ] AS statement"},"ManualEvidence":{},"MeasuredEvidence":{}},"Text":{"Collection":"sql","Key":"prepare","SourceDatabase":"pgweb","Version":"18","Locale":"zh-Hans","Title":"PREPARE","Summary":"预备一个语句以供执行","BodyHTML":"\u003cpre\u003ePREPARE name [ ( data_type [, ...] ) ] AS statement\u003c/pre\u003e\u003csection\u003e\u003ch2\u003e描述\u003c/h2\u003e\u003cp\u003e\u003ccode\u003ePREPARE\u003c/code\u003e 创建一个预备语句。预备语句是一种服务器端对象，可用于优化性能。执行 \u003ccode\u003ePREPARE\u003c/code\u003e 语句时，指定的语句会被解析、分析并重写。随后发出 \u003ccode\u003eEXECUTE\u003c/code\u003e 命令时，该预备语句会被规划并执行。这种分工避免了重复的解析分析工作，同时又允许执行计划依赖于所提供的特定参数值。\u003c/p\u003e\u003cp\u003e预备语句可以带参数，也就是在执行时会代入语句中的值。创建预备语句时，可以按位置引用参数，例如 \u003ccode\u003e$1\u003c/code\u003e、\u003ccode\u003e$2\u003c/code\u003e 等。也可以选择指定相应的参数数据类型列表。如果某个参数的数据类型未指定，或被声明为 \u003ccode\u003eunknown\u003c/code\u003e，则其类型会从该参数第一次被引用时所在的上下文中推断出来（如果可能）。执行该语句时，需要在 \u003ccode\u003eEXECUTE\u003c/code\u003e 语句中为这些参数提供实际值。有关详情，参见\u003ca href=\"/docs/18/sql-execute.html\" title=\"EXECUTE\" rel=\"nofollow\"\u003e\u003cspan\u003eEXECUTE\u003c/span\u003e\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e预备语句只在当前数据库会话期间存在。会话结束时，预备语句就会被遗忘，因此再次使用前必须重新创建。这也意味着单个预备语句不能由多个并发的数据库客户端共用；不过，每个客户端都可以创建自己要使用的预备语句。预备语句也可以用\u003ca href=\"/docs/18/sql-deallocate.html\" title=\"DEALLOCATE\" rel=\"nofollow\"\u003e\u003ccode\u003eDEALLOCATE\u003c/code\u003e\u003c/a\u003e 命令手工释放。\u003c/p\u003e\u003cp\u003e当单个会话要执行大量相似语句时，预备语句可能带来最大的性能优势。如果语句在规划或重写时比较复杂，例如查询涉及许多表的连接，或者需要应用多条规则，那么这种性能差异会特别明显。如果语句相对容易规划和重写，但执行本身代价较高，则预备语句带来的性能优势就不那么明显。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e参数\u003c/h2\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e为这个特定预备语句指定的任意名称。它在单个会话内必须唯一，随后用于执行或释放先前已准备的语句。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003edata_type\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e预备语句中某个参数的数据类型。如果某个参数的数据类型未指定，或被指定为 \u003ccode\u003eunknown\u003c/code\u003e，则其类型会从该参数第一次被引用时所在的上下文中推断出来。要在预备语句本身中引用参数，可使用 \u003ccode\u003e$1\u003c/code\u003e、\u003ccode\u003e$2\u003c/code\u003e 等。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003estatement\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e任何\u003ccode\u003eSELECT\u003c/code\u003e、\u003ccode\u003eINSERT\u003c/code\u003e、\u003ccode\u003eUPDATE\u003c/code\u003e、\u003ccode\u003eDELETE\u003c/code\u003e、\u003ccode\u003eMERGE\u003c/code\u003e或\u003ccode\u003eVALUES\u003c/code\u003e语句。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e注解\u003c/h2\u003e\u003cp\u003e预备语句既可以用\u003cem\u003e通用计划\u003c/em\u003e执行，也可以用\u003cem\u003e自定义计划\u003c/em\u003e执行。通用计划在所有执行中都相同，而自定义计划则是针对某次特定执行、根据该次调用给出的参数值生成的。使用通用计划可以避免规划开销，但在某些情况下，自定义计划的执行效率会高得多，因为规划器可以利用参数值信息。（当然，如果预备语句没有参数，这一点就无从谈起，并且总是会使用通用计划。）\u003c/p\u003e\u003cp\u003e默认情况下，也就是当\u003ca href=\"/docs/18/runtime-config-query.html#GUC-PLAN-CACHE-MODE\" rel=\"nofollow\"\u003eplan_cache_mode\u003c/a\u003e设置为 \u003ccode\u003eauto\u003c/code\u003e 时，服务器会自动为带参数的预备语句选择使用通用计划还是自定义计划。当前规则是：前五次执行使用自定义计划，并计算这些计划的平均估计代价；然后创建一个通用计划，并将其估计代价与自定义计划的平均代价比较。如果通用计划的代价没有比自定义计划的平均代价高到足以让反复重新规划显得更可取的程度，那么后续执行就会使用通用计划。\u003c/p\u003e\u003cp\u003e这种启发式规则可以被覆盖。将 \u003ccode\u003eplan_cache_mode\u003c/code\u003e 分别设置为 \u003ccode\u003eforce_generic_plan\u003c/code\u003e 或 \u003ccode\u003eforce_custom_plan\u003c/code\u003e，即可强制服务器使用通用计划或自定义计划。如果由于某种原因，通用计划的代价估计严重失真，导致它虽然被选中但实际代价远高于自定义计划，那么这个设置就尤其有用。\u003c/p\u003e\u003cp\u003e要查看 \u003cspan\u003ePostgreSQL\u003c/span\u003e 为预备语句使用的查询计划，可以使用\u003ca href=\"/docs/18/sql-explain.html\" title=\"EXPLAIN\" rel=\"nofollow\"\u003e\u003ccode\u003eEXPLAIN\u003c/code\u003e\u003c/a\u003e，例如：\u003c/p\u003e\u003cpre\u003eEXPLAIN EXECUTE \u003cem\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e(\u003cem\u003e\u003ccode\u003eparameter_values\u003c/code\u003e\u003c/em\u003e);\n\u003c/pre\u003e\u003cp\u003e如果使用的是通用计划，其中会包含参数符号 \u003ccode\u003e$\u003cem\u003e\u003ccode\u003en\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e；而自定义计划则会把提供的参数值代入其中。\u003c/p\u003e\u003cp\u003e有关查询规划以及 \u003cspan\u003ePostgreSQL\u003c/span\u003e 为此收集的统计信息的更多内容，请参见\u003ca href=\"/docs/18/sql-analyze.html\" title=\"ANALYZE\" rel=\"nofollow\"\u003e\u003cspan\u003eANALYZE\u003c/span\u003e\u003c/a\u003e文档。\u003c/p\u003e\u003cp\u003e尽管预备语句的主要目的在于避免对语句重复进行解析分析和规划，但只要语句中使用的数据库对象自上次使用该预备语句以来发生了定义（DDL）变更，或者它们的规划器统计信息已被更新，\u003cspan\u003ePostgreSQL\u003c/span\u003e 就会在再次使用前强制重新分析并重新规划该语句。此外，如果\u003ca href=\"/docs/18/runtime-config-client.html#GUC-SEARCH-PATH\" rel=\"nofollow\"\u003esearch_path\u003c/a\u003e的值在两次使用之间发生变化，该语句也会基于新的 \u003ccode\u003esearch_path\u003c/code\u003e 重新解析。（后一种行为是从 \u003cspan\u003ePostgreSQL\u003c/span\u003e 9.3 开始引入的。）这些规则使得使用预备语句在语义上几乎等同于反复重新提交同一段查询文本，同时在对象定义未改变时仍能获得性能收益，特别是在跨多次使用时最佳计划保持不变的情况下。这种语义等价性并不完全成立。举例来说，如果语句以未限定名引用某个表，随后又在 \u003ccode\u003esearch_path\u003c/code\u003e 中更靠前的某个模式里创建了一个同名新表，那么不会自动触发重新解析，因为语句所使用的对象本身并未改变。不过，如果后来由于其他变更而触发了重新解析，后续使用中引用的就会是这个新表。\u003c/p\u003e\u003cp\u003e可以通过查询\u003ca href=\"/docs/18/view-pg-prepared-statements.html\" rel=\"nofollow\"\u003e\u003ccode\u003epg_prepared_statements\u003c/code\u003e\u003c/a\u003e 系统视图，查看当前会话中所有可用的预备语句。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e示例\u003c/h2\u003e\u003cp\u003e为一个\u003ccode\u003eINSERT\u003c/code\u003e语句创建一个预备语句，然后执行它：\u003c/p\u003e\u003cpre\u003ePREPARE fooplan (int, text, bool, numeric) AS\n    INSERT INTO foo VALUES($1, $2, $3, $4);\nEXECUTE fooplan(1, \u0026#39;Hunter Valley\u0026#39;, \u0026#39;t\u0026#39;, 200.00);\n\u003c/pre\u003e\u003cp\u003e为一个\u003ccode\u003eSELECT\u003c/code\u003e语句创建一个预备语句，然后执行它：\u003c/p\u003e\u003cpre\u003ePREPARE usrrptplan (int) AS\n    SELECT * FROM users u, logs l WHERE u.usrid=$1 AND u.usrid=l.usrid\n    AND l.date = $2;\nEXECUTE usrrptplan(1, current_date);\n\u003c/pre\u003e\u003cp\u003e在这个示例中，第二个参数的数据类型没有指定，因此会从 \u003ccode\u003e$2\u003c/code\u003e 的使用上下文中推断出来。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e兼容性\u003c/h2\u003e\u003cp\u003eSQL 标准包含\u003ccode\u003ePREPARE\u003c/code\u003e语句，但它只用于嵌入式 SQL。这里的\u003ccode\u003ePREPARE\u003c/code\u003e语句在语法上也略有不同。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e另见\u003c/h2\u003e\u003cspan\u003e\u003ca href=\"/wiki/sql/deallocate/?v=18\" title=\"DEALLOCATE\" rel=\"nofollow\"\u003e\u003cspan\u003eDEALLOCATE\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/execute/?v=18\" title=\"EXECUTE\" rel=\"nofollow\"\u003e\u003cspan\u003eEXECUTE\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e\u003c/section\u003e","SourceRevision":"1b5ca64c","ContentHash":"c1e52b505a1e919e08733668fee3d722ee00527c272ef16041e4230e24e4b55e","Payload":{"purpose_zh":"预备一个语句以供执行","sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003ePREPARE\u003c/code\u003e 创建一个预备语句。预备语句是一种服务器端对象，可用于优化性能。执行 \u003ccode class=\"command\"\u003ePREPARE\u003c/code\u003e 语句时，指定的语句会被解析、分析并重写。随后发出 \u003ccode class=\"command\"\u003eEXECUTE\u003c/code\u003e 命令时，该预备语句会被规划并执行。这种分工避免了重复的解析分析工作，同时又允许执行计划依赖于所提供的特定参数值。\u003c/p\u003e\u003cp\u003e预备语句可以带参数，也就是在执行时会代入语句中的值。创建预备语句时，可以按位置引用参数，例如 \u003ccode class=\"literal\"\u003e$1\u003c/code\u003e、\u003ccode class=\"literal\"\u003e$2\u003c/code\u003e 等。也可以选择指定相应的参数数据类型列表。如果某个参数的数据类型未指定，或被声明为 \u003ccode class=\"literal\"\u003eunknown\u003c/code\u003e，则其类型会从该参数第一次被引用时所在的上下文中推断出来（如果可能）。执行该语句时，需要在 \u003ccode class=\"command\"\u003eEXECUTE\u003c/code\u003e 语句中为这些参数提供实际值。有关详情，参见\u003ca href=\"/docs/18/sql-execute.html\" title=\"EXECUTE\"\u003e\u003cspan class=\"refentrytitle\"\u003eEXECUTE\u003c/span\u003e\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e预备语句只在当前数据库会话期间存在。会话结束时，预备语句就会被遗忘，因此再次使用前必须重新创建。这也意味着单个预备语句不能由多个并发的数据库客户端共用；不过，每个客户端都可以创建自己要使用的预备语句。预备语句也可以用\u003ca href=\"/docs/18/sql-deallocate.html\" title=\"DEALLOCATE\"\u003e\u003ccode class=\"command\"\u003eDEALLOCATE\u003c/code\u003e\u003c/a\u003e 命令手工释放。\u003c/p\u003e\u003cp\u003e当单个会话要执行大量相似语句时，预备语句可能带来最大的性能优势。如果语句在规划或重写时比较复杂，例如查询涉及许多表的连接，或者需要应用多条规则，那么这种性能差异会特别明显。如果语句相对容易规划和重写，但执行本身代价较高，则预备语句带来的性能优势就不那么明显。\u003c/p\u003e","key":"description","title":"描述"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e为这个特定预备语句指定的任意名称。它在单个会话内必须唯一，随后用于执行或释放先前已准备的语句。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003edata_type\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e预备语句中某个参数的数据类型。如果某个参数的数据类型未指定，或被指定为 \u003ccode class=\"literal\"\u003eunknown\u003c/code\u003e，则其类型会从该参数第一次被引用时所在的上下文中推断出来。要在预备语句本身中引用参数，可使用 \u003ccode class=\"literal\"\u003e$1\u003c/code\u003e、\u003ccode class=\"literal\"\u003e$2\u003c/code\u003e 等。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003estatement\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e任何\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e、\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e、\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e、\u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e、\u003ccode class=\"command\"\u003eMERGE\u003c/code\u003e或\u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e语句。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"参数"},{"html":"\u003cp\u003e预备语句既可以用\u003cem class=\"firstterm\"\u003e通用计划\u003c/em\u003e执行，也可以用\u003cem class=\"firstterm\"\u003e自定义计划\u003c/em\u003e执行。通用计划在所有执行中都相同，而自定义计划则是针对某次特定执行、根据该次调用给出的参数值生成的。使用通用计划可以避免规划开销，但在某些情况下，自定义计划的执行效率会高得多，因为规划器可以利用参数值信息。（当然，如果预备语句没有参数，这一点就无从谈起，并且总是会使用通用计划。）\u003c/p\u003e\u003cp\u003e默认情况下，也就是当\u003ca href=\"/docs/18/runtime-config-query.html#GUC-PLAN-CACHE-MODE\"\u003eplan_cache_mode\u003c/a\u003e设置为 \u003ccode class=\"literal\"\u003eauto\u003c/code\u003e 时，服务器会自动为带参数的预备语句选择使用通用计划还是自定义计划。当前规则是：前五次执行使用自定义计划，并计算这些计划的平均估计代价；然后创建一个通用计划，并将其估计代价与自定义计划的平均代价比较。如果通用计划的代价没有比自定义计划的平均代价高到足以让反复重新规划显得更可取的程度，那么后续执行就会使用通用计划。\u003c/p\u003e\u003cp\u003e这种启发式规则可以被覆盖。将 \u003ccode class=\"varname\"\u003eplan_cache_mode\u003c/code\u003e 分别设置为 \u003ccode class=\"literal\"\u003eforce_generic_plan\u003c/code\u003e 或 \u003ccode class=\"literal\"\u003eforce_custom_plan\u003c/code\u003e，即可强制服务器使用通用计划或自定义计划。如果由于某种原因，通用计划的代价估计严重失真，导致它虽然被选中但实际代价远高于自定义计划，那么这个设置就尤其有用。\u003c/p\u003e\u003cp\u003e要查看 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 为预备语句使用的查询计划，可以使用\u003ca href=\"/docs/18/sql-explain.html\" title=\"EXPLAIN\"\u003e\u003ccode class=\"command\"\u003eEXPLAIN\u003c/code\u003e\u003c/a\u003e，例如：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eEXPLAIN EXECUTE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e(\u003cem class=\"replaceable\"\u003e\u003ccode\u003eparameter_values\u003c/code\u003e\u003c/em\u003e);\n\u003c/pre\u003e\u003cp\u003e如果使用的是通用计划，其中会包含参数符号 \u003ccode class=\"literal\"\u003e$\u003cem class=\"replaceable\"\u003e\u003ccode\u003en\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e；而自定义计划则会把提供的参数值代入其中。\u003c/p\u003e\u003cp\u003e有关查询规划以及 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 为此收集的统计信息的更多内容，请参见\u003ca href=\"/docs/18/sql-analyze.html\" title=\"ANALYZE\"\u003e\u003cspan class=\"refentrytitle\"\u003eANALYZE\u003c/span\u003e\u003c/a\u003e文档。\u003c/p\u003e\u003cp\u003e尽管预备语句的主要目的在于避免对语句重复进行解析分析和规划，但只要语句中使用的数据库对象自上次使用该预备语句以来发生了定义（DDL）变更，或者它们的规划器统计信息已被更新，\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 就会在再次使用前强制重新分析并重新规划该语句。此外，如果\u003ca href=\"/docs/18/runtime-config-client.html#GUC-SEARCH-PATH\"\u003esearch_path\u003c/a\u003e的值在两次使用之间发生变化，该语句也会基于新的 \u003ccode class=\"varname\"\u003esearch_path\u003c/code\u003e 重新解析。（后一种行为是从 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 9.3 开始引入的。）这些规则使得使用预备语句在语义上几乎等同于反复重新提交同一段查询文本，同时在对象定义未改变时仍能获得性能收益，特别是在跨多次使用时最佳计划保持不变的情况下。这种语义等价性并不完全成立。举例来说，如果语句以未限定名引用某个表，随后又在 \u003ccode class=\"varname\"\u003esearch_path\u003c/code\u003e 中更靠前的某个模式里创建了一个同名新表，那么不会自动触发重新解析，因为语句所使用的对象本身并未改变。不过，如果后来由于其他变更而触发了重新解析，后续使用中引用的就会是这个新表。\u003c/p\u003e\u003cp\u003e可以通过查询\u003ca href=\"/docs/18/view-pg-prepared-statements.html\" title=\"53.16. pg_prepared_statements\"\u003e\u003ccode class=\"structname\"\u003epg_prepared_statements\u003c/code\u003e\u003c/a\u003e 系统视图，查看当前会话中所有可用的预备语句。\u003c/p\u003e","key":"notes","title":"注解"},{"html":"\u003cp\u003e为一个\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e语句创建一个预备语句，然后执行它：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003ePREPARE fooplan (int, text, bool, numeric) AS\n    INSERT INTO foo VALUES($1, $2, $3, $4);\nEXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);\n\u003c/pre\u003e\u003cp\u003e为一个\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e语句创建一个预备语句，然后执行它：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003ePREPARE usrrptplan (int) AS\n    SELECT * FROM users u, logs l WHERE u.usrid=$1 AND u.usrid=l.usrid\n    AND l.date = $2;\nEXECUTE usrrptplan(1, current_date);\n\u003c/pre\u003e\u003cp\u003e在这个示例中，第二个参数的数据类型没有指定，因此会从 \u003ccode class=\"literal\"\u003e$2\u003c/code\u003e 的使用上下文中推断出来。\u003c/p\u003e","key":"examples","title":"示例"},{"html":"\u003cp\u003eSQL 标准包含\u003ccode class=\"command\"\u003ePREPARE\u003c/code\u003e语句，但它只用于嵌入式 SQL。这里的\u003ccode class=\"command\"\u003ePREPARE\u003c/code\u003e语句在语法上也略有不同。\u003c/p\u003e","key":"compatibility","title":"兼容性"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/deallocate/?v=18\" title=\"DEALLOCATE\"\u003e\u003cspan class=\"refentrytitle\"\u003eDEALLOCATE\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/execute/?v=18\" title=\"EXECUTE\"\u003e\u003cspan class=\"refentrytitle\"\u003eEXECUTE\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"另见"}],"sections_same_as":"","synopsis_html":"PREPARE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e [ ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003edata_type\u003c/code\u003e\u003c/em\u003e [, ...] ) ] AS \u003cem class=\"replaceable\"\u003e\u003ccode\u003estatement\u003c/code\u003e\u003c/em\u003e","synopsis_text":"PREPARE name [ ( data_type [, ...] ) ] AS statement"}},"RequestedLocale":"zh-Hans","Fallback":false,"Versions":["10","11","12","13","14","15","16","17","18","19","20","7.3","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6"],"Locales":["en","zh-Hans"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}