• MySQL知识库 :: configuration
  • What is Server SQL Mode?

  • Discussion

    The MySQL server can operate in different SQL modes, and (as of MySQL version 4.1) it can apply these modes separately for different clients. Modes define which SQL syntax MySQL should support and what kind of data validation checks it should perform. This makes it easier to use MySQL in different environments and to use MySQL together with other database servers.

    You can set the default SQL mode by starting mysqld with the --sql-mode option. The value typically would contain one or more modes, separated by commans, However, it can be set to empty if you want to reset it. You can also change the SQL mode after startup time by setting the sql_mode variable using a SET [SESSION|GLOBAL] sql_mode='modes' statement. Setting the GLOBAL variable requires the SUPER privilege and affects the operation of all clients that connect starting from the time it's set. Setting the SESSION variable affects only the current client. Any client can change its own session sql_mode value at any time.

    You can retrieve the current mode by issuing a SELECT @@sql_mode statement. The default value is empty. The most important sql_mode values are these:

    • ANSI
    • Change syntax and behavior to be more conformant to standard SQL.


      If a value could not be inserted as given into a transactional table, abort the statement. For a non-transactional table, abort the statement if the value occurs in a single-row statement or the first row of a multiple-row statement. More detail is given later in this section--implemented in MySQL 5.0.2.

    • Make MySQL behave like a “traditional” SQL database system. A simple description of this mode is “give an error instead of a warning” when inserting an incorrect value into a column. Note: The INSERT/UPDATE aborts as soon as the error is noticed. This may not be what you want if you are using a non-transactional storage engine, because data changes made prior to the error are not be rolled back, resulting in a “partially done” update--dded in MySQL 5.0.2.

    For more information, please see the external resources link in the margin.