MySQL SQL Mode

最新推荐文章于 2024-07-20 20:04:43 发布

crj30556

最新推荐文章于 2024-07-20 20:04:43 发布

阅读量85

点赞数

文章标签：数据库

1.MySQL SQL Mode介绍

通过SQL Mode ，可以完成不同严格程度的校验，有效的保障数据的准确性。

(1)查看默认的SQL Mode的命令:

mysql> select @@sql_mode;

+--------------------------------------------+

| @@sql_mode |

+--------------------------------------------+

| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |

+--------------------------------------------+

1 row in set (0.00 sec)

(2)修改SQL Mode

1 2	mysql> set session sql_mode='REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI'; Query OK, 0 rows affected (0.00 sec)

(3)查看表结构

mysql> desc test;

+-------+----------+------+-----+---------+-------+

+-------+----------+------+-----+---------+-------+

| id | char(10) | YES | | NULL | |

+-------+----------+------+-----+---------+-------+

1 row in set (0.00 sec)

(4)插入一条记录故意超出定义的范围

mysql> insert into test values('12300000000000000000000000000000');

Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> show warnings;

+---------+------+-----------------------------------------+

| Level | Code | Message |

+---------+------+-----------------------------------------+

| Warning | 1265 | Data truncated for column 'id' at row 1 |

+---------+------+

-----------------------------------------+

1 row in set (0.00 sec)

mysql> select * from test;

+------------+

| id |

+------------+

| 10 |

| 1230000000 |

+------------+

2 rows in set (0.00 sec)

(5)修改SQL Mode模式为严格模式

1 2	mysql> set session sql_mode='STRICT_TRANS_TABLES'; Query OK, 0 rows affected (0.00 sec)

(6)再次插入数据超出范围

1 2	mysql> insert into test values('12300000000000000000000000000000'); ERROR 1406 (22001): Data too long for column 'id' at row 1

这次没有插入成功而是报错了。

2.SQL Mode的常见功能

(1)校验日期数据合法性

mysql> set session sql_mode='STRICT_TRANS_TABLES';

Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values('12300000000000000000000000000000');

ERROR 1406 (22001): Data too long for column 'id' at row 1

mysql> set session sql_mode='ANSI';

Query OK, 0 rows affected (0.00 sec)

mysql> create table t(d datetime);

Query OK, 0 rows affected (0.20 sec)

mysql> insert into t values('2007-04-31');

Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from t;

+---------------------+

| d |

+---------------------+

| 0000-00-00 00:00:00 |

+---------------------+

1 row in set (0.00 sec)

mysql> set session sql_mode='TRADITIONAL';

Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values('2007-04-31');

ERROR 1292 (22007): Incorrect datetime value: '2007-04-31' for column 'd' at row 1

在ANSI模式下可以插入数据，但是数据是‘0000-00-00 00:00:00'

在TRADITIONAL模式下会执行失败。

(2)咋insert ，update 过程中，如果SQL Mode模式为TRADITIONAL下运行MOD(X,0)就会产生错误。

mysql> set sql_mode='ANSI'

-> ;

Query OK, 0 rows affected (0.00 sec)

mysql> create table t1(i int);

Query OK, 0 rows affected (0.25 sec)

mysql> insert into t1 values(9%0);

Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;

+------+

| i |

+------+

| NULL |

+------+

1 row in set (0.00 sec)

mysql> set session sql_mode='TRADITIONAL';

Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(9%0);

ERROR 1365 (22012): Division by 0

(3)启动NO_BACKSLASH_ESCAPES模式，使用反斜杠成为普通字符。

mysql> set sql_mode='ansi';

Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;

+-------------------------------------------------------------+

| @@sql_mode |

+-------------------------------------------------------------+

| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |

+-------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> create table t2(context varchar(20));

Query OK, 0 rows affected (0.14 sec)

mysql> insert into t2 values('\beijing');

Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;

+---------+

| context |

+---------+

|eijing |

+---------+

1 row in set (0.00 sec)

mysql> insert into t2 values('\\beijing');

Query OK, 1 row affected (0.03 sec)

mysql> select * from t2;

+----------+

| context |

+----------+

|eijing |

| \beijing |

+----------+

2 rows in set (0.00 sec)

mysql> set session sql_mode='REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,NO_BACKSLASH_ESCAPES';

Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2 values('\\beijing');

Query OK, 1 row affected (0.01 sec)

mysql> select * from t2;

+-----------+

| context |

+-----------+

|eijing |

| \beijing |

| \\beijing |

+-----------+

3 rows in set (0.00 sec)

(4)启用PIPES_AS_CONCAT模式，将“||”视为字符串连接操作。

mysql> set sql_mode='ansi';

Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;

+-------------------------------------------------------------+

| @@sql_mode |

+-------------------------------------------------------------+

| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |

+-------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> select 'beijing'||'2008';

+-------------------+

| 'beijing'||'2008' |

+-------------------+

| beijing2008 |

+-------------------+

1 row in set (0.00 sec)

3.常用的SQL Mode

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

4.SQL Mode 在迁移中如何使用

如果mysql与其它异构数据库之间有数据移植的需求的话，那么下面的sql_mode的组合设置可以达到相应的效果：

数据库	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