dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
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. modes is a list of different modes separated by comma (",") characters.
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. The most important sql_mode values are probably these: * ANSI This mode changes syntax and behavior to conform more closely to standard SQL. 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. 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 may not be rolled back, resulting in a "partially done" update. 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). It does not apply TIMESTAMP columns, which always require a valid date. 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. You can still use "" to quote identifiers with this mode enabled. With ANSI_QUOTES enabled, you cannot use double quotes to quote literal strings, because it is interpreted as an identifier.
If this mode is not enabled, MySQL instead returns NULL for divisions by zero. For INSERT IGNORE or UPDATE IGNORE, MySQL generates a warning for divisions by zero, but the result of the operation is NULL.
NOT operator is such that expressions such as NOT a BETWEEN b AND c are parsed as NOT (a BETWEEN b AND c). This causes built-in function names to be treated as reserved words.
COUNT() function, the use of count as a table name in the following statement causes an error: mysql> CREATE TABLE count (i INT); ERROR 1064 (42000): You have an error in your SQL syntax The table name should be quoted: mysql> CREATE TABLE count (i INT); It is always allowable to have spaces after a UDF or stored function name, regardless of whether IGNORE_SPACE is enabled.
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. 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. With this mode enabled, backslash becomes an ordinary character like any other. If the desired engine is invalid (not a known engine name), an error occurs and the table is not created or altered. With NO_ENGINE_SUBSTITUTION enabled, an error occurs and the table is not created or altered if the desired engine is unavailable for any reason (whether disabled or invalid). In other words, the result of a subtraction is always signed whenever this mode is in effect, even if one of the operands is unsigned. For example, compare the type of column c2 in table t1 with that of column c2 in table t2: mysql> SET SQL_MODE='';
When not in strict mode, the date is accepted but a warning is generated. 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. The following query is invalid with this mode enabled because address is not named in the GROUP BY clause: SELECT name, address, MAX(age) FROM t GROUP BY name;
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 non-NULL column that has no explicit DEFAULT clause in its definition. 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. 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.
Strict mode disallows invalid date values such as '2004-04-31'. It does not disallow dates with zero month or day 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.
The following special modes are provided as shorthand for combinations of mode values from the preceding list. For older versions, a combination mode does not include individual mode values that are not available except in newer versions.
|
http://csua.com/feed/