SqlTemplate sql模板方案 sql-mode

转载

mob6454cc6441b6 2024-04-01 09:20:44

文章标签 SqlTemplate sql模板方案 mysql模式数据库模式 ansi strict_trans_tables 文章分类 机器学习人工智能

前言

Github：https://github.com/yihonglei/road-of-arch/tree/master/thinking-in-mysql

概述

MySql 可以运行在不同的 sql 模式，不同的模式支持的语法和数据校验不同。

SQL MODE 有哪些作用？

1）通过设置 SQL MODE 可以完成不同严格程度的数据校验，有效地保证数据库数据的准确性。

2）设置 SQL MODE 为 ANSI 模式，保证大多数 sql 符合标准 sql 语法，方便数据库迁移。

3）通过严格或非严格模式设置，方便数据库的迁移。

一常用sql模式

1、常用三种 sql 模式

ANSI

非严格模式，等同于 REAL_AS_FLOAT，PIPES_AS_CONCAT，ANSI_QUOTES，IGNORE_SPACE，ONLY_FULL_GROUP_BY，ANSI 的组合，

这种模式的语法和行为更符合标准的 SQL。

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。

适用于事务表和非事务表，对于插入不正确数据，直接报错。

2、ANSI 模式实例

mysql> set sql_mode='ANSI';
Query OK, 0 rows affected, 1 warning (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> create table t_user_main1(f_userName varchar(4),f_password varchar(4));
Query OK, 0 rows affected (0.69 sec)

mysql> insert into t_user_main1 values('aaaaa','bbbbb');
Query OK, 1 row affected, 2 warnings (0.05 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1265 | Data truncated for column 'f_userName' at row 1 |
| Warning | 1265 | Data truncated for column 'f_password' at row 1 |
+---------+------+-------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from t_user_main1;
+------------+------------+
| f_userName | f_password |
+------------+------------+
| aaaa       | bbbb       |
+------------+------------+
1 row in set (0.00 sec)

从执行结果可以看出，数据库在 ANSI 模式下对数据校验是非严格程度，如果数据插入过长，数据库自动截掉多出的部分，

自行处理插入数据库，给出的只是一个警告。可以通过 show warnings 列表式查看警告的详细信息。

3、STRICT_TRANS_TABLES 模式实例

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

mysql> select @@sql_mode;
+---------------------+
| @@sql_mode          |
+---------------------+
| STRICT_TRANS_TABLES |
+---------------------+
1 row in set (0.00 sec)

mysql> create table t_user_main2(f_userName varchar(4),f_password varchar(4));
Query OK, 0 rows affected (0.89 sec)

mysql> insert into t_user_main2 values('aaaaa','bbbbb');
ERROR 1406 (22001): Data too long for column 'f_userName' at row 1
mysql> show errors;
+-------+------+------------------------------------------------+
| Level | Code | Message                                        |
+-------+------+------------------------------------------------+
| Error | 1406 | Data too long for column 'f_userName' at row 1 |
+-------+------+------------------------------------------------+
1 row in set (0.00 sec)

通过执行结果可以看出来，使用严格模式STRICT_TRANS_TABLES时，数据库对插入的数据做严格校验，

如果插入的数据超过长度，直接报错。可以通过show errors列表式查看错误的详细信息。

4、TRADITIONAL 模式实例

mysql> set sql_mode = 'TRADITIONAL';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @@sql_mode;
+---------------------------------------------------------------------------------------------
----------------+
| @@sql_mode
                |
+---------------------------------------------------------------------------------------------
----------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZER
NE_SUBSTITUTION |
+---------------------------------------------------------------------------------------------
----------------+
1 row in set (0.00 sec)

mysql> create table t_user_main3 (f_userName varchar(4),f_password varchar(4));
Query OK, 0 rows affected (0.41 sec)

mysql> insert into t_user_main3 values('aaaaa','bbbbb');
ERROR 1406 (22001): Data too long for column 'f_userName' at row 1
mysql> show errors;
+-------+------+------------------------------------------------+
| Level | Code | Message                                        |
+-------+------+------------------------------------------------+
| Error | 1406 | Data too long for column 'f_userName' at row 1 |
+-------+------+------------------------------------------------+
1 row in set (0.00 sec)

通过执行结果可以看出来，使用严格模式STRICT_TRANS_TABLES时，数据库对插入的数据做严格校验，

如果插入的数据超过长度，直接报错。可以通过show errors列表式查看错误的详细信息。