MySQL 5.7에서 SQL_MODE를 설정하는 방법

sql_mode는 간과하기 쉬운 변수입니다. 5.5의 기본값은 null입니다. 이 설정에서는 일부 불법적인 데이터 삽입을 허용하는 등 일부 불법적인 작업이 허용될 수 있습니다.

이 값 설정은 5.6에서 강화되었으며 5.7에서는 보안 규정에 더 많은 관심을 기울였습니다. 이 값은 기본적으로 strict 모드입니다

1. sql_mode는 다음 유형의 문제를 해결하는 데 사용됩니다

SQL 모드를 설정하여 다른 엄격함을 적용합니다. 수준을 달성할 수 있습니다. 데이터 검증은 데이터 준비 상태를 효과적으로 보장합니다.

SQL 모드를 완화 모드로 설정하면 대부분의 SQL이 표준 SQL 구문을 준수하도록 보장됩니다. 이러한 방식으로 애플리케이션이 서로 다른 데이터베이스 간에 마이그레이션될 때 비즈니스 SQL을 크게 수정할 필요가 없습니다. 대상 데이터베이스로 쉽게 마이그레이션할 수 있습니다.

2. MySQL5.7의 sql_mode 매개변수 기본값에 대한 설명(다음은 MySQL 5.7.27 버전)

• ONLY_FULL_GROUP_BYONLY_FULL_GROUP_BY

对于使用 GROUP BY 进行查询的SQL，不允许 SELECT 部分出现 GROUP BY 中未出现的字段，也就是 SELECT 查询的字段必须是 GROUP BY 中出现的或者使用聚合函数的或者是具有唯一属性的。

create table test(name varchar(10),value int);
insert into test values ('a',1),('a',20),('b',23),('c',15),('c',30);
#默认情况是可能会写出无意义或错误的聚合语句：
SET sql_mode='';
select * from test group by name;
select value,sum(value) from test group by name;
# 使用该模式后，写法必须标准
SET sql_mode='ONLY_FULL_GROUP_BY';
select name,sum(value) from test group by name;
-- 错误写法则报错
select value,sum(value) from test group by name;
# 报错终止
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.test.value' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

• STRICT_TRANS_TABLES

这个选项只对事务型存储引擎起作用，对非事务型存储引擎无效，其作用是启用严格 SQL 模式。在strict sql模式下，在INSERT或者UPDATE语句中，插入或者更新了某个不符合规定的字段值，则会直接报错中断操作

create table test(value int(1));
SET sql_mode=''; #默认只要第一个值
 
insert into test(value) values('a'),(1); #不报错
insert into test(value) values(2),('a'); #不报错
select * from test;
+------------+
| value      |
+------------+
|          0 |
|          1 |
|          2 |
|          0 |
+------------+
#后面删除表不再说明！
drop table test; 
create table test(value int(1));
 
SET sql_mode='STRICT_TRANS_TABLES'; #每个值都判断
 
insert into test(value) values('a'),(1);
#报错，第一行'a'错误。
ERROR 1366 (HY000): Incorrect integer value: 'a' for column 'value' at row 1

• NO_ZERO_IN_DATE

MySQL中插入的时间字段值，不允许日期和月份为零

create table test(value date);
SET sql_mode='';
insert into test(value) values('2020-00-00'); #结果为 '2020-00-00'
 
SET sql_mode='NO_ZERO_IN_DATE';
insert into test(value) values('2021-00-00'); #不符合,转为 '0000-00-00'

• NO_ZERO_DATE

MySQL中插入的时间字段值，不允许插入 ‘0000-00-00’ 日期

create table test(value date);
 
SET sql_mode='';
insert into test(value) values('0000-00-00'); #无警告 warning
 
SET sql_mode='STRICT_TRANS_TABLES';
insert into test(value) values('0000-00-00'); #无警告 warning
 
SET sql_mode='NO_ZERO_DATE';
insert into test(value) values('0000-00-00'); #有警告 warning
 
SET sql_mode='NO_ZERO_DATE,STRICT_TRANS_TABLES'
insert into test(value) values('0000-00-00');
# 报错终止
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'value' at row 1

• ERROR_FOR_DIVISION_BY_ZERO

INSERT或者UPDATE语句中，如果数据被0除，则出现警告（非strict sql模式下）或者错误(strict sql模式下)。

• 当该选项关闭时，数字被0除，得到NULL且不会产生警告

• 当该选项开启且处于非strict sql模式下，数字被0除，得到NULL但是会产生警告

• 当该选项开启且处于strict sql模式下，数字被0除，产生错误且中断操作

create table test(value int);
 
SET sql_mode='';  
select 10/0;  #无警告 warning
insert into test(value) values(10/0);   #无警告 warning
 
SET sql_mode='STRICT_TRANS_TABLES'; 
select 10/0;   #无警告 warning
insert into test(value) values(10/0);  #无警告 warning
 
SET sql_mode='ERROR_FOR_DIVISION_BY_ZERO'; 
select 10/0;  #有警告 warning
insert into test(value) values(10/0);  #有警告 warning
 
SET sql_mode='ERROR_FOR_DIVISION_BY_ZERO,STRICT_TRANS_TABLES';
select 10/0; #有警告 warning
insert into test(value) values(10/0); 
#报错：ERROR 1365 (22012): Division by 0

• NO_AUTO_CREATE_USER

禁止GRANT创建密码为空的用户

SET sql_mode='';
grant all on test.* to test01@'localhost';  #不报错（无需要设置密码）
SET sql_mode='NO_AUTO_CREATE_USER';
# 报错
ERROR 1133 (42000): Can't find any matching row in the user table

#正确 写法，需要设置密码
grant all on test.* to test01@'localhost' identified by 'test01...';

• NO_ENGINE_SUBSTITUTION

GROUP BY를 사용하는 SQL의 경우 쿼리의 경우 허용되지 않습니다. GROUP BY에 나타나지 않는 필드는 SELECT 부분에 나타납니다. 즉, SELECT 쿼리의 필드는 GROUP BY에 나타나거나 집계 함수를 사용하거나 고유한 속성을 가져야 합니다.

# 查看当前支持的存储引擎
show engines;

set sql_mode='';
create table test(id int) ENGINE="test";
Query OK, 0 rows affected, 2 warnings (0.03 sec)

select table_name,engine from information_schema.tables where table_schema='test' and table_name='test'; # 转为默认存储引擎
+------------+--------+
| table_name | engine |
+------------+--------+
| test       | InnoDB |
+------------+--------+
SET sql_mode='NO_ENGINE_SUBSTITUTION';
create table test(id int) ENGINE=test;
# 报错
ERROR 1286 (42000): Unknown storage engine 'test'

STRICT_TRANS_TABLES

이 옵션은 트랜잭션 스토리지 엔진에만 작동하며 비트랜잭션 스토리지 엔진에는 유효하지 않습니다. 해당 기능은 엄격한 SQL 모드를 활성화하는 것입니다. 엄격한 SQL 모드에서 요구 사항을 충족하지 않는 필드 값이 INSERT 또는 UPDATE 문에 삽입되거나 업데이트되면 오류가 직접 보고되어 작업이 중단됩니다🎜

> show variables like '%sql_mode%';
> set @@sql_mode="NO_ENGINE_SUBSTITUTION"
> set session sql_mode='STRICT_TRANS_TABLES';

🎜🎜🎜NO_ZERO_IN_DATE🎜 🎜🎜🎜MySQL MySQL에 삽입된 시간 필드 값은 날짜와 월이 0이 되는 것을 허용하지 않습니다.🎜

# vim /etc/my.cnf
[mysqld]
......
sql_mode="NO_ENGINE_SUBSTITUTION"
......

🎜🎜🎜NO_ZERO_DATE🎜🎜🎜🎜MySQL에 삽입된 시간 필드 값은 삽입을 허용하지 않습니다. of ‘0000-00-00’ Date 🎜rrreee🎜🎜🎜ERROR_FOR_DIVISION_BY_ZERO🎜🎜🎜🎜 INSERT 또는 UPDATE 문에서 데이터가 0으로 나누어지면 경고(엄격하지 않은 SQL에서는) 모드) 또는 오류(엄격한 SQL 모드에서)가 나타납니다. 🎜🎜🎜🎜이 옵션이 꺼지면 숫자가 0으로 나누어지고 NULL이 얻어지며 경고가 생성되지 않습니다.🎜🎜🎜🎜이 옵션이 켜져 있고 Non-Strict SQL 모드에서 숫자가 0으로 나누어집니다. , NULL을 얻었지만 경고가 발생합니다🎜 🎜🎜🎜이 옵션이 켜져 있고 strict sql 모드에서 숫자가 0으로 나누어지면 오류가 발생하고 작업이 중단됩니다🎜🎜🎜rrreee🎜🎜🎜 NO_AUTO_CREATE_USER🎜🎜🎜🎜GRANT에서 빈 비밀번호 사용자 🎜rrreee🎜🎜🎜NO_ENGINE_SUBSTITUTION🎜🎜🎜🎜CREATE TABLE 또는 ALTER TABLE 구문을 사용하여 스토리지 엔진을 실행하는 경우 , 설정된 스토리지 엔진이 비활성화되거나 컴파일되지 않으면 오류가 발생합니다. 🎜rrreee🎜3.sql_mode 설정 및 수정🎜🎜방법 1: 수정 가능한 전역 변수입니다🎜rrreee🎜방법 2: 구성 파일 수정(적용하려면 다시 시작해야 함)🎜rrreee

위 내용은 MySQL 5.7에서 SQL_MODE를 설정하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!