DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK
 

(mysql.info) server-sql-mode

Info Catalog (mysql.info) server-status-variables (mysql.info) mysqld (mysql.info) server-shutdown
 
 5.2.5 The Server SQL Mode
 -------------------------
 
 The MySQL server can operate in different SQL modes, and can apply
 these modes differently for different clients. This capability enables
 each application to tailor the server's operating mode to its own
 requirements.
 
 Modes define what 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="MODES" option. MODES is a list of different modes separated
 by comma (‘`,'’) characters. The default value is empty (no modes
 set). The MODES value also can be empty (-sql-mode="") if you want to
 clear it explicitly.
 
 You can change the SQL mode at runtime by using a `SET [GLOBAL|SESSION]
 sql_mode='MODES'' statement to set the `sql_mode' system value.
 Setting the `GLOBAL' variable requires the `SUPER' privilege and
 affects the operation of all clients that connect from that time on.
 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 global or session `sql_mode' value with
 the following statements:
 
      SELECT @@global.sql_mode;
      SELECT @@session.sql_mode;
 
 The most important `sql_mode' values are probably these:
 
    * `ANSI'
 
      Change syntax and behavior to be more conformant to standard SQL.
 
    * `STRICT_TRANS_TABLES'
 
      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)
 
    * `TRADITIONAL'
 
      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.  *
      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. (Added
      in MySQL 5.0.2)
 
 When this manual refers to `strict mode,' it means a mode where at
 least one of `STRICT_TRANS_TABLES' or `STRICT_ALL_TABLES' is enabled.
 
 The following list describes all supported modes:
 
    * `ALLOW_INVALID_DATES'
 
      Don't do full checking of dates. Check only that the month is in
      the range from 1 to 12 and the day is in the range from 1 to 31.
      This is very convenient for Web applications where you obtain
      year, month, and day in three different fields and you want to
      store exactly what the user inserted (without date validation).
      This mode applies to `DATE' and `DATETIME' columns. It does not
      apply `TIMESTAMP' columns, which always require a valid date.
 
      This mode is implemented in MySQL 5.0.2. Before 5.0.2, this was
      the default MySQL date-handling mode. As of 5.0.2, the server
      requires that month and day values be legal, and not merely in the
      range 1 to 12 and 1 to 31, respectively. With strict mode
      disabled, invalid dates such as `'2004-04-31'' are converted to
      `'0000-00-00'' and a warning is generated. With strict mode
      enabled, invalid dates generate an error. To allow such dates,
      enable `ALLOW_INVALID_DATES'.
 
    * `ANSI_QUOTES'
 
      Treat ‘`"'’ as an identifier quote character (like the
      ‘``'’ quote character) and not as a string quote character.
      You can still use ‘``'’ to quote identifiers with this mode
      enabled. With `ANSI_QUOTES' enabled, you cannot use double quotes
      to quote a literal string, because it is interpreted as an
      identifier.
 
    * `ERROR_FOR_DIVISION_BY_ZERO'
 
      Produce an error in strict mode (otherwise a warning) when we
      encounter a division by zero (or `MOD(X,0)') during an `INSERT' or
      `UPDATE'. If this mode is not enabled, MySQL instead returns
      `NULL' for divisions by zero. If used in `INSERT IGNORE' or `UPDATE
      IGNORE', MySQL generates a warning for divisions by zero, but the
      result of the operation is `NULL'. (Implemented in MySQL 5.0.2)
 
    * `HIGH_NOT_PRECEDENCE'
 
      From MySQL 5.0.2 on, the precedence of the `NOT' operator is such
      that expressions such as `NOT a BETWEEN b AND c' are parsed as
      `NOT (a BETWEEN b AND c)'.  Before MySQL 5.0.2, the expression is
      parsed as `(NOT a) BETWEEN b AND c'. The old higher-precedence
      behavior can be obtained by enabling the `HIGH_NOT_PRECEDENCE' SQL
      mode. (Added in MySQL 5.0.2)
 
           mysql> SET sql_mode = '';
           mysql> SELECT NOT 1 BETWEEN -5 AND 5;
                   -> 0
           mysql> SET sql_mode = 'broken_not';
           mysql> SELECT NOT 1 BETWEEN -5 AND 5;
                   -> 1
 
    * `IGNORE_SPACE'
 
      Allow spaces between a function name and the ‘`('’ character.
      This forces all function names to be treated as reserved words. As
      a result, if you want to access any database, table, or column
      name that is a reserved word, you must quote it.  For example,
      because there is a `USER()' function, the name of the `user' table
      in the `mysql' database and the `User' column in that table become
      reserved, so you must quote them:
 
           SELECT "User" FROM mysql."user";
 
      The `IGNORE_SPACE' SQL mode applies to built-in functions, not to
      stored routines. it is always allowable to have spaces after a
      routine name, regardless of whether `IGNORE_SPACE' is enabled.
 
    * `NO_AUTO_CREATE_USER'
 
      Prevent `GRANT' from automatically creating new users if it would
      otherwise do so, unless a non-empty password also is specified.
      (Added in MySQL 5.0.2)
 
    * `NO_AUTO_VALUE_ON_ZERO'
 
      `NO_AUTO_VALUE_ON_ZERO' affects handling of `AUTO_INCREMENT'
      columns. Normally, you generate the next sequence number for the
      column by inserting either `NULL' or `0' into it.
      `NO_AUTO_VALUE_ON_ZERO' suppresses this behavior for `0' so that
      only `NULL' generates the next sequence number.
 
      This mode can be useful if `0' has been stored in a table's
      `AUTO_INCREMENT' column. (Storing `0' is not a recommended
      practice, by the way.) For example, if you dump the table with
      `mysqldump' and then reload it, MySQL normally generates new
      sequence numbers when it encounters the `0' values, resulting in a
      table with contents different from the one that was dumped.
      Enabling `NO_AUTO_VALUE_ON_ZERO' before reloading the dump file
      solves this problem.  `mysqldump' now automatically includes in
      its output a statement that enables `NO_AUTO_VALUE_ON_ZERO', to
      avoid this problem.
 
    * `NO_BACKSLASH_ESCAPES'
 
      Disable the use of the backslash character (‘`\'’) as an escape
      character within strings. With this mode enabled, backslash
      becomes any ordinary character like any other.  (Implemented in
      MySQL 5.0.1)
 
    * `NO_DIR_IN_CREATE'
 
      When creating a table, ignore all `INDEX DIRECTORY' and `DATA
      DIRECTORY' directives. This option is useful on slave replication
      servers.
 
    * `NO_ENGINE_SUBSTITUTION'
 
      Prevents automatic substitution of the default storage engine when
      a statement such as `CREATE TABLE' specifies a storage engine that
      is disabled or not compiled in. (Implemented in MySQL 5.0.8)
 
    * `NO_FIELD_OPTIONS'
 
      Do not print MySQL-specific column options in the output of `SHOW
      CREATE TABLE'. This mode is used by `mysqldump' in portability
      mode.
 
    * `NO_KEY_OPTIONS'
 
      Do not print MySQL-specific index options in the output of `SHOW
      CREATE TABLE'. This mode is used by `mysqldump' in portability
      mode.
 
    * `NO_TABLE_OPTIONS'
 
      Do not print MySQL-specific table options (such as `ENGINE') in
      the output of `SHOW CREATE TABLE'. This mode is used by
      `mysqldump' in portability mode.
 
    * `NO_UNSIGNED_SUBTRACTION'
 
      In subtraction operations, do not mark the result as `UNSIGNED' if
      one of the operands is unsigned. Note that this makes `BIGINT
      UNSIGNED' not 100% usable in all contexts. See 
      cast-functions.
 
    * `NO_ZERO_DATE'
 
      In strict mode, don't allow `'0000-00-00'' as a valid date. You can
      still insert zero dates with the `IGNORE' option. When not in
      strict mode, the date is accepted but a warning is generated.
      (Added in MySQL 5.0.2)
 
    * `NO_ZERO_IN_DATE'
 
      In strict mode, don't accept dates where the month or day part is
      0. If used with the `IGNORE' option, MySQL inserts a `'0000-00-00''
      date for any such date. When not in strict mode, the date is
      accepted but a warning is generated. (Added in MySQL 5.0.2)
 
    * `ONLY_FULL_GROUP_BY'
 
      Do not allow queries for which the `GROUP BY' clause refers to a
      column that is not present in the output column list.
 
    * `PIPES_AS_CONCAT'
 
      Treat `||' as a string concatenation operator (same as `CONCAT()')
      rather than as a synonym for `OR'.
 
    * `REAL_AS_FLOAT'
 
      Treat `REAL' as a synonym for `FLOAT'. By default, MySQL treats
      `REAL' as a synonym for `DOUBLE'.
 
    * `STRICT_ALL_TABLES'
 
      Enable strict mode for all storage engines. Invalid data values
      are rejected. Additional detail follows. (Added in MySQL 5.0.2)
 
    * `STRICT_TRANS_TABLES'
 
      Enable strict mode for transactional storage engines, and when
      possible for non-transactional storage engines.  Additional
      details follow. (Implemented in MySQL 5.0.2)
 
 Strict mode controls how MySQL handles input values that are invalid or
 missing. A value can be invalid for several reasons. For example, it
 might have the wrong data type for the column, or it might be out of
 range. A value is missing when a new row to be inserted does not
 contain a value for a column that has no explicit `DEFAULT' clause in
 its definition.
 
 For transactional tables, an error occurs for invalid or missing values
 in a statement when either of the `STRICT_ALL_TABLES' or
 `STRICT_TRANS_TABLES' modes are enabled. The statement is aborted and
 rolled back.
 
 For non-transactional tables, the behavior is the same for either mode,
 if the bad value occurs in the first row to be inserted or updated. The
 statement is aborted and the table remains unchanged. If the statement
 inserts or modifies multiple rows and the bad value occurs in the
 second or later row, the result depends on which strict option is
 enabled:
 
    * For `STRICT_ALL_TABLES', MySQL returns an error and ignores the
      rest of the rows. However, in this case, the earlier rows still
      have been inserted or updated. This means that you might get a
      partial update, which might not be what you want. To avoid this,
      it's best to use single-row statements because these can be aborted
      without changing the table.
 
    * For `STRICT_TRANS_TABLES', MySQL converts an invalid value to the
      closest valid value for the column and insert the adjusted value.
      If a value is missing, MySQL inserts the implicit default value
      for the column data type. In either case, MySQL generates a warning
      rather than an error and continues processing the statement.
      Implicit defaults are described in  data-type-defaults.
 
 Strict mode disallows invalid date values such as `'2004-04-31''. It
 does not disallow dates with zero parts such as `'2004-04-00'' or
 `zero' dates. To disallow these as well, enable the `NO_ZERO_IN_DATE'
 and `NO_ZERO_DATE' SQL modes in addition to strict mode.
 
 If you are not using strict mode (that is, neither
 `STRICT_TRANS_TABLES' nor `STRICT_ALL_TABLES' is enabled), MySQL
 inserts adjusted values for invalid or missing values and produces
 warnings. In strict mode, you can produce this behavior by using
 `INSERT IGNORE' or `UPDATE IGNORE'. See  show-warnings.
 
 The following special modes are provided as shorthand for combinations
 of mode values from the preceding list. All are available in MySQL 5.0
 beginning with version 5.0.0, except for `TRADITIONAL', which was
 implemented in MySQL 5.0.2.
 
 The descriptions include all mode values that are available in the most
 recent version of MySQL. For older versions, a combination mode does
 not include individual mode values that are not available except in
 newer versions.
 
    * `ANSI'
 
      Equivalent to `REAL_AS_FLOAT', `PIPES_AS_CONCAT', `ANSI_QUOTES',
      `IGNORE_SPACE'. Before MySQL 5.0.3, `ANSI' also includes
      `ONLY_FULL_GROUP_BY'. See  ansi-mode.
 
    * `DB2'
 
      Equivalent to `PIPES_AS_CONCAT', `ANSI_QUOTES', `IGNORE_SPACE',
      `NO_KEY_OPTIONS', `NO_TABLE_OPTIONS', `NO_FIELD_OPTIONS'.
 
    * `MAXDB'
 
      Equivalent to `PIPES_AS_CONCAT', `ANSI_QUOTES', `IGNORE_SPACE',
      `NO_KEY_OPTIONS', `NO_TABLE_OPTIONS', `NO_FIELD_OPTIONS',
      `NO_AUTO_CREATE_USER'.
 
    * `MSSQL'
 
      Equivalent to `PIPES_AS_CONCAT', `ANSI_QUOTES', `IGNORE_SPACE',
      `NO_KEY_OPTIONS', `NO_TABLE_OPTIONS', `NO_FIELD_OPTIONS'.
 
    * `MYSQL323'
 
      Equivalent to `NO_FIELD_OPTIONS', `HIGH_NOT_PRECEDENCE'.
 
    * `MYSQL40'
 
      Equivalent to `NO_FIELD_OPTIONS', `HIGH_NOT_PRECEDENCE'.
 
    * `ORACLE'
 
      Equivalent to `PIPES_AS_CONCAT', `ANSI_QUOTES', `IGNORE_SPACE',
      `NO_KEY_OPTIONS', `NO_TABLE_OPTIONS', `NO_FIELD_OPTIONS',
      `NO_AUTO_CREATE_USER'.
 
    * `POSTGRESQL'
 
      Equivalent to `PIPES_AS_CONCAT', `ANSI_QUOTES', `IGNORE_SPACE',
      `NO_KEY_OPTIONS', `NO_TABLE_OPTIONS', `NO_FIELD_OPTIONS'.
 
    * `TRADITIONAL'
 
      Equivalent to `STRICT_TRANS_TABLES', `STRICT_ALL_TABLES',
      `NO_ZERO_IN_DATE', `NO_ZERO_DATE', `ERROR_FOR_DIVISION_BY_ZERO',
      `NO_AUTO_CREATE_USER'.
 
Info Catalog (mysql.info) server-status-variables (mysql.info) mysqld (mysql.info) server-shutdown
automatically generated byinfo2html