DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH
 

(mysql.info.gz) Server SQL mode

Info Catalog (mysql.info.gz) Server options (mysql.info.gz) Configuring MySQL (mysql.info.gz) Server system variables
 
 5.2.2 The Server SQL Mode
 -------------------------
 
 The MySQL server can operate in different SQL modes, and (as of MySQL
 4.1) can apply these modes differentially for different clients. This
 allows an application to tailor server operation 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.  The value also can be empty
 (`--sql-mode=""') if you want to reset it.
 
 Beginning with MySQL 4.1, you can also change the SQL mode after
 startup time by setting the `sql_mode' variable with 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 from that time on. Setting the `SESSION'
 variable affects only the current client.  Any client can change its
 session `sql_mode' value.
 
 MODES is a list of different modes separated by comma (`,') characters.
 You can retrieve the current mode by issuing a `SELECT @@sql_mode'
 statement. The default value is empty (no modes set).
 
 The most important `sql_mode' values are probably these:
 
 `ANSI'
      Change syntax and behavior to be more conformant to standard SQL.
      (New in MySQL 4.1.1)
 
 `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.  (New 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' will abort 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 will
      not be rolled back, resulting in a "partially-done" update.  (New
      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 the supported modes:
 
 `ALLOW_INVALID_DATES'
      Don't do full checking of dates in strict mode. 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 new in MySQL 5.0.2. Before 5.0.2, this was the
      default MySQL date-handling mode. As of 5.0.2, enabling strict
      mode causes the server to require that month and day values be
      legal, not just in the range from 1 to 12 and 1 to 31. For
      example, `'2004-04-31'' is legal with strict mode disabled, but
      illegal with strict mode enabled. To allow such dates in strict
      mode, enable `ALLOW_INVALID_DATES' as well.
 
 `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 in ANSI mode. With `ANSI_QUOTES' enabled,
      you cannot use double quotes to quote a literal string, because it
      will be interpreted as an identifier.  (New in MySQL 4.0.0)
 
 `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'/
      `UPDATE'. If this mode is not given, MySQL instead returns `NULL'
      for divisions by zero.  If used with `IGNORE', MySQL generates a
      warning for divisions by zero, but the result of the operation is
      `NULL'.  (New in MySQL 5.0.2)
 
 `HIGH_NOT_PRECEDENCE'
      From MySQL 5.0.2 on, the `NOT' operator precedence is handled so
      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.  (New 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";
 
      (New in MySQL 4.0.0)
 
 `NO_AUTO_CREATE_USER'
      Prevent `GRANT' from automatically creating new users if it would
      otherwise do so, unless a password also is specified.  (New 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.  (New in MySQL
      4.1.1)
 
      This mode can be useful if `0' has been stored in a table's
      `AUTO_INCREMENT' column. (This 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 different
      contents than the one that was dumped. Enabling
      `NO_AUTO_VALUE_ON_ZERO' before reloading the dump file solves this
      problem. As of MySQL 4.1.1, `mysqldump' automatically includes a
      statement in the dump output to enable `NO_AUTO_VALUE_ON_ZERO'.
 
 `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.  (New 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.  (New in MySQL 4.0.15)
 
 `NO_FIELD_OPTIONS'
      Don't print MySQL-specific column options in the output of `SHOW
      CREATE TABLE'. This mode is used by `mysqldump' in portability
      mode.  (New in MySQL 4.1.1)
 
 `NO_KEY_OPTIONS'
      Don't print MySQL-specific index options in the output of `SHOW
      CREATE TABLE'. This mode is used by `mysqldump' in portability
      mode.  (New in MySQL 4.1.1)
 
 `NO_TABLE_OPTIONS'
      Don't print MySQL-specific table options (such as `ENGINE') in the
      output of `SHOW CREATE TABLE'. This mode is used by `mysqldump' in
      portability mode.  (New in MySQL 4.1.1)
 
 `NO_UNSIGNED_SUBTRACTION'
      In subtraction operations, don't mark the result as `UNSIGNED' if
      one of the operands is unsigned. Note that this makes `UNSIGNED
      BIGINT' not 100% usable in all contexts.  Cast Functions.
      (New in MySQL 4.0.2)
 
 `NO_ZERO_DATE'
      Don't allow `'0000-00-00'' as a valid date. You can still insert
      zero dates with the `IGNORE' option.  (New in MySQL 5.0.2)
 
 `NO_ZERO_IN_DATE'
      Don't accept dates where the month or day part is 0. If used with
      the `IGNORE' option, we insert a `'0000-00-00'' date for any such
      date.  (New in MySQL 5.0.2)
 
 `ONLY_FULL_GROUP_BY'
      Don't allow queries that in the `GROUP BY' part refer to a not
      selected column.  (New in MySQL 4.0.0)
 
 `PIPES_AS_CONCAT'
      Treat `||' as a string concatenation operator (same as `CONCAT()')
      rather than as a synonym for `OR'.  (New in MySQL 4.0.0)
 
 `REAL_AS_FLOAT'
      Treat `REAL' as a synonym for `FLOAT' rather than as a synonym for
      `DOUBLE'.  (New in MySQL 4.0.0)
 
 `STRICT_ALL_TABLES'
      Enable strict mode for all storage engines.  Invalid data values
      are rejected.  Additional detail follows.  (New in MySQL 5.0.2)
 
 `STRICT_TRANS_TABLES'
      Enable strict mode for transactional storage engines, and when
      possible for non-transactional storage engines.  Additional detail
      follows.  (New in MySQL 5.0.2)
 
 
 Strict mode controls how MySQL handles 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 will
      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  `CREATE
      TABLE' CREATE TABLE.
 
 
 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'.
  `SHOW WARNINGS' SHOW WARNINGS.
 
 The following special modes are provided as shorthand for combinations
 of mode values from the preceding list.  All are available as of MySQL
 4.1.1, except `TRADITIONAL' (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', `ONLY_FULL_GROUP_BY'.  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.gz) Server options (mysql.info.gz) Configuring MySQL (mysql.info.gz) Server system variables
automatically generated byinfo2html