my SQL的demo数据库

转载

mob64ca141275de 2024-07-17 23:17:09

文章标签 my SQL的demo数据库数据库 mysql sql SQL 文章分类 机器学习人工智能

SQL Mode简介

在MySQL中，SQL Mode常常用来解决以下问题：

1.通过设置SQL Mode，可以完成不同严格程度的数据校验，有效保证数据准确性。
2.通过设置SQL Mode为ANSI模式，来保证大多数SQL是符合标准的SQL语法，这样应用在不同数据库之间迁移时，则不需要对业务SQL进行较大修改。
3.在不同数据库进行数据迁移时，通过设置SQL Mode可以使得MySQL上的数据更方便迁移到目标数据库

在MySQL5.7.18上，查询默认的SQL Mode(@@sql_mode)为STRICT_TRANS_TABLES, NO_ENGINE_SUBSTITUTION

STRICT_TRANS_TABLES为严格模式，实现了数据的严格校验，对不符合数据类型的错误数据不能插入表中，保证了数据的准确性。

mysql> select @@sql_mode;  # 严格模式
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> desc transaction;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20) | YES  | UNI | NULL    |                |
| account | double      | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql>insert into transaction values(null, "123456789abcdefghijkkkkkkkkkkkkkkk", 2000);
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> select @@sql_mode;  # 严格模式
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> desc transaction;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20) | YES  | UNI | NULL    |                |
| account | double      | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql>insert into transaction values(null, "123456789abcdefghijkkkkkkkkkkkkkkk", 2000);
ERROR 1406 (22001): Data too long for column 'name' at row 1

如果更改SQL Mode为ANSI模式，错误数据也会被插入表中, 对于超过长度的值，会进行截取。

mysql> set session sql_mode="ANSI";  #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,ONLY_FULL_GROUP_BY,ANSI |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into transaction values(null,'1111111111111111222222222222223333333333333',2000);
Query OK, 1 row affected, 1 warning (0.10 sec)

mysql> select name from transaction where id=27;  # 允许数据插入但多余长度被截断
+----------------------+
| name                 |
+----------------------+
| 11111111111111112222 |
+----------------------+
1 row in set (0.00 sec)
mysql> set session sql_mode="ANSI";  #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,ONLY_FULL_GROUP_BY,ANSI |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into transaction values(null,'1111111111111111222222222222223333333333333',2000);
Query OK, 1 row affected, 1 warning (0.10 sec)

mysql> select name from transaction where id=27;  # 允许数据插入但多余长度被截断
+----------------------+
| name                 |
+----------------------+
| 11111111111111112222 |
+----------------------+
1 row in set (0.00 sec)

SQL Mode的常见功能

1 校验日期格式的合法性

例如：给定一个非法日期如“2019-2-31”进行插入操作

mysql> select @@sql_mode;  # ANSI模式
+--------------------------------------------------------------------------------+
| @@sql_mode                                                                     |
+--------------------------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create table t1(d date);
Query OK, 0 rows affected (0.29 sec)

mysql> SET @invalidData='2017-2-31';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(@invalidData);
Query OK, 1 row affected, 1 warning (0.07 sec)

mysql> select * from t1;  # ANSI模式支持非法数据插入，插入值为“0000-00-00"
+------------+
| d          |
+------------+
| 0000-00-00 |
+------------+
1 row in set (0.00 sec)

mysql> set session sql_mode="TRADITIONAL";  # 严格模式下报错
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t1 values(@invalidData);
ERROR 1292 (22007): Incorrect date value: '2017-2-31' for column 'd' at row 1
mysql>
mysql> select @@sql_mode;  # ANSI模式
+--------------------------------------------------------------------------------+
| @@sql_mode                                                                     |
+--------------------------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create table t1(d date);
Query OK, 0 rows affected (0.29 sec)

mysql> SET @invalidData='2017-2-31';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(@invalidData);
Query OK, 1 row affected, 1 warning (0.07 sec)

mysql> select * from t1;  # ANSI模式支持非法数据插入，插入值为“0000-00-00"
+------------+
| d          |
+------------+
| 0000-00-00 |
+------------+
1 row in set (0.00 sec)

mysql> set session sql_mode="TRADITIONAL";  # 严格模式下报错
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t1 values(@invalidData);
ERROR 1292 (22007): Incorrect date value: '2017-2-31' for column 'd' at row 1
mysql>

2 在`ANSI`模式下执行`MOD(x, 0)`不会出错，插入时数值变为`NULL`。

3 启用`NO_BACKSLASH_ESCAPES`使得反斜杠成普通字符。

mysql> select @@sql_mode;  #ANSI模式下，没有启用NO_BACKSLASH_ESCAPES
+--------------------------------------------------------------------------------+
| @@sql_mode                                                                     |
+--------------------------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> set @escapeStr = '\\a\s\t\n123';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2 values(@escapeStr);
Query OK, 1 row affected (0.06 sec)

mysql> select * from t2;    # 出现转义字符被执行
+----------+
| url      |
+----------+
| \as
123 |
+----------+
1 row in set (0.00 sec)

mysql> set session sql_mode='ANSI,NO_BACKSLASH_ESCAPES';
Query OK, 0 rows affected (0.00 sec)

mysql> set @escapeStr = '\\begin';
Query OK, 0 rows affected (0.00 sec)

mysql> truncate table t2;
Query OK, 0 rows affected (0.20 sec)

mysql> insert into t2 values(@escapeStr);
Query OK, 1 row affected (0.04 sec)

mysql> select * from t2;
+---------+
| url     |
+---------+
| \\begin |
+---------+
1 row in set (0.00 sec)

mysql>
mysql> select @@sql_mode;  #ANSI模式下，没有启用NO_BACKSLASH_ESCAPES
+--------------------------------------------------------------------------------+
| @@sql_mode                                                                     |
+--------------------------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> set @escapeStr = '\\a\s\t\n123';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2 values(@escapeStr);
Query OK, 1 row affected (0.06 sec)

mysql> select * from t2;    # 出现转义字符被执行
+----------+
| url      |
+----------+
| \as
123 |
+----------+
1 row in set (0.00 sec)

mysql> set session sql_mode='ANSI,NO_BACKSLASH_ESCAPES';
Query OK, 0 rows affected (0.00 sec)

mysql> set @escapeStr = '\\begin';
Query OK, 0 rows affected (0.00 sec)

mysql> truncate table t2;
Query OK, 0 rows affected (0.20 sec)

mysql> insert into t2 values(@escapeStr);
Query OK, 1 row affected (0.04 sec)

mysql> select * from t2;
+---------+
| url     |
+---------+
| \\begin |
+---------+
1 row in set (0.00 sec)

mysql>

4 启用PIPES_AS_CONTACT模式。将“`||`”视为字符串连接符号(同`CONCAT`)

mysql> select '1'||'2' as A, concat('3', '4') as B;
+----+----+
| A  | B  |
+----+----+
| 12 | 34 |
+----+----+
1 row in set (0.00 sec)
mysql> select '1'||'2' as A, concat('3', '4') as B;
+----+----+
| A  | B  |
+----+----+
| 12 | 34 |
+----+----+
1 row in set (0.00 sec)

常见的SQL Mode

sql_mode值	描述
ANSI	等同于REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI
STRICT_TRANS_TABLES	适用于事务处理，对于非法数据直接抛出错误，而非warning
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，属于严格模式，对于非法数据直接抛出错误，可用在事务表中，出现错误立即回滚

SQL Mode在数据迁移中如何使用

MySQL提供了很多数据库的组合模式，例如“ORACLE、POSTGRESQL”等

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：vlan1为什么会环路

下一篇：node npm yarn的关系

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

my SQL的demo数据库

my SQL的demo数据库

SQL Mode简介

SQL Mode的常见功能

1 校验日期格式的合法性

2 在ANSI模式下执行MOD(x, 0)不会出错，插入时数值变为NULL。

3 启用NO_BACKSLASH_ESCAPES使得反斜杠成普通字符。

4 启用PIPES_AS_CONTACT模式。将“||”视为字符串连接符号(同CONCAT)

常见的SQL Mode

SQL Mode在数据迁移中如何使用

51CTO博客

2 在`ANSI`模式下执行`MOD(x, 0)`不会出错，插入时数值变为`NULL`。

3 启用`NO_BACKSLASH_ESCAPES`使得反斜杠成普通字符。

4 启用PIPES_AS_CONTACT模式。将“`||`”视为字符串连接符号(同`CONCAT`)