Mysql SQL Mode

  • 2020-06-19 11:55:14
  • OfStack

Introduction to Mysql SQL Mode

In general, MySQL servers can work in different SQL patterns and apply these patterns in different ways for different clients. In this way, the application can customize the server operation to suit its own needs. This pattern defines the SQL syntax that MySQL should support and what validation checks should be performed on the data. This makes MySQL easier to use in many different environments, starting with other database servers. You can set the default SQL mode by starting mysqld using the "-- sql-ES12en =" option. Starting from MySQL 4.1, you can also change the schema by setting the sql_mode variable using the SET [SESSION|GLOBAL] sql_mode='modes' statement after startup.

After usually installed under linux mysql, its default sql - mode value is empty, in which case mysql execution is one kind of not strict inspection, such as date fields can be inserted into the '0000-00-00 00:00:00 such values, and if you want to insert the field length more than the length of the column definition, then mysql not termination of operations, but the character after the truncation to automatically insert, the following cases:


mysql> create table t5 (c1 char(3));
mysql> insert into t5 values('abcd');
mysql> select * from t5;
+------+
| c1 |
+------+
| abc |
+------+
1 row in set (0.00 sec)

We found that the inserted character was automatically truncated, but if we wanted to report an error if the length exceeded the limit, we could set sql_mode to STRICT_TRANS_TABLES, as follows:


mysql> set session sql_mode='STRICT_TRANS_TABLES'

If we do the same, mysql will tell us that the inserted value is too long and the operation is terminated, as follows:


mysql> insert into t5 values('abcd');
ERROR 1406 (22001): Data too long for column 'c1' at row 1

Frequently used sql_mode value:

Sql_mode值 描述
ANSI 更改语法和行为,使其更符合标准SQL。
STRICT_TRANS_TABLES 如果不能将给定的值插入到事务表中,则放弃该语句。对于非事务表,如果值出现在单行语句或多行语句的第1行,则放弃该语句。本节后面给出了更详细的描述。
TRADITIONAL Make MySQL的行为象“传统”SQL数据库系统。该模式的简单描述是当在列中插入不正确的值时“给出错误而不是警告”。注释:1旦发现错误立即放弃INSERT/UPDATE。如果你使用非事务存储引擎,这种方式不是你想要的,因为出现错误前进行的数据更改不会“滚动”,结果是更新“只进行了1部分”。

Note: If you set the value of sql_mode to the next two values (which is what we call strict mode), mysql will give you an error when inserting or updating an incorrect value in a column and abandon the insert/update operation. We recommend using both modes in our general application, rather than using the default null or ANSI modes. But need to pay attention to the problem is that if the database to run in strict mode, and your storage engine does not support transactions, then there is the risk of data is not 1 to exist, such as 1 group has two sql dml statement, if the one behind the problems, but has been operating in front of success, so mysql doesn't roll back in front of the operation. So setting up sql_mode requires the application to weigh the pros and cons to get an appropriate choice.

There are many more values for Sql_mode, which will not be covered here, but refer to the relevant manual.

2. SQL Mode and portability

If there is a need for data migration between mysql and other heterogeneous databases, then the following combination of sql_mode can achieve the corresponding effect:

数据库 Sql_mode值
DB2 PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS
MAXDB PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、 NO_AUTO_CREATE_USER
MSSQL PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、 NO_FIELD_OPTIONS
ORACLE PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、NO_AUTO_CREATE_USER
POSTGRESQL PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS

3. SQL Mode and data validity

SQL Mode can also achieve data validation and transfer functions, such as:

1. Validity of validity date data.
2. During INSERT or UPDATE, an error occurs if it is divided by zero (or MOD(X, 0))
3. Treat '"' as an identifier quote ('' quote character)
4. Disable the backslash character (' \') as an exit character within a string. With NO_BACKSLASH_ESCAPES mode enabled, backslashes become plain characters.
5. Treat || as the string concatenation operator (+) (the same as CONCAT()) instead of OR.


Related articles: