SQL MODE

SQL MODE: 定义msyqld对约束等的响应行为；

常用mode: TRADITIONAL, STRICT_TRANS_TABLES, or STRICT_ALL_TABLES
修改方式：

mysql> SET GLOBAL sql_mode='MODE';
mysql> SET @@global.sql_mode='MODE';

仅对修改后新创建的会话有效；对已经建立的会话无效；

修改当前会话的sql mode:

mysql> SET SESSION sql_mode='MODE';
mysql> SET @@session.sql_mode='MODE';

sql mode示例：

查询默认的sql mode:

[mydb]> show global variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+

创建数据库mydb，创建表t1(id,name);插入数据'tom'和'blackberry';默认sql mode是空的，当字符超过给定的字符长度时会被截短保存：

[(none)]> create database mydb;
[mydb]> create table t1(id int unsigned auto_increment primary key not null, name char(5) not null);
[mydb]> insert into t1(name) values('tom'),('blackberry');
Query OK, 2 rows affected, 1 warning (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 1
[mydb]> show warnings;
+---------+------+-------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'name' at row 2 |
+---------+------+-------------------------------------------+
[mydb]> select * from t1;
+----+-------+
| id | name |
+----+-------+
| 1 | tom |
| 2 | black |
+----+-------+

如果这种超限的字符串不让它插入到表中，我们可以修改sql mode:

[mydb]> set global sql_mode='TRADITIONAL';
[mydb]> insert into t1 values(null,'redberry');
ERROR 1406 (22001): Data too long for column 'name' at row 1

要想设置永久有效，需要在mysql配置文件中设定。