MySQL管理与优化(12)：SQL Mode及相关问题

最新推荐文章于 2024-07-13 18:10:19 发布

weixin_34064653

最新推荐文章于 2024-07-13 18:10:19 发布

阅读量76

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/indestiny/blog/282644

版权

2019独角兽企业重金招聘Python工程师标准>>>

SQL Mode及相关问题

SQL Mode可以解决几类问题：

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

2. 通过设置SQL Mode为ANSI模式，来保证大多数SQL符合标准的SQL语法，这样应用在不同数据库之间进行迁移时，则不需要对业务SQL进行较大的修改。

3. 在不同数据库之间进行数据迁移之前，通过设置SQL Mode可以使MySQL上的数据更方便地迁移到目标数据库中。

查看默认地SQL Mode:

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 sqlmode_test(
    -> name VARCHAR(10));
Query OK, 0 rows affected (0.03 sec)

-- 插入一个超长的记录，会出现提醒
mysql> INSERT INTO sqlmode_test VALUES ('abcdefghiklmnopqrstuvwxyz');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SHOW warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'name' at row 1 |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)

-- 数据被截断了
mysql> SELECT * FROM sqlmode_test;
+------------+
| name       |
+------------+
| abcdefghik |
+------------+
1 row in set (0.00 sec)

-- 修改SQL_MODE，再次插入，会报错
mysql> SET SESSION sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO sqlmode_test VALUES ('abcdefghiklmnopqrstuvwxyz');
ERROR 1406 (22001): Data too long for column 'name' at row 1

TRADITIONAL：MOD(?, 0)会报错，不正确的日期也会报错。
NO_BACKSLASH_ESCAPES：使反斜杠\成为普通字符。
PIPES_AS_CONCAT: 使用||作为字符串连接符。

常用的一些SQL Mode：

sql_mode值	描述
ANSI	REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI的组合
STRICT_TRANS_TABLES	使用于事务表和非事务表，属于严格模式，不允许非法日期，也不允许超长字段，对于不正确的值，都会给出错误。
TRADITIONAL	STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO, TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION的组合