create table test(name varchar(10),value int);
insert into test values ('a',1),('a',20),('b',23),('c',15),('c',30);
#默認(rèn)情況是可能會(huì)寫出無意義或錯(cuò)誤的聚合語句：
SET sql_mode='';
select * from test group by name;
select value,sum(value) from test group by name;
# 使用該模式后，寫法必須標(biāo)準(zhǔn)
SET sql_mode='ONLY_FULL_GROUP_BY';
select name,sum(value) from test group by name;
-- 錯(cuò)誤寫法則報(bào)錯(cuò)
select value,sum(value) from test group by name;
# 報(bào)錯(cuò)終止
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

create table test(value int(1));
SET sql_mode=''; #默認(rèn)只要第一個(gè)值
?
insert into test(value) values('a'),(1); #不報(bào)錯(cuò)
insert into test(value) values(2),('a'); #不報(bào)錯(cuò)
select * from test;
+------------+
| value ? ? ?|
+------------+
| ? ? ? ? ?0 |
| ? ? ? ? ?1 |
| ? ? ? ? ?2 |
| ? ? ? ? ?0 |
+------------+
#后面刪除表不再說明！
drop table test;?
create table test(value int(1));
?
SET sql_mode='STRICT_TRANS_TABLES'; #每個(gè)值都判斷
?
insert into test(value) values('a'),(1);
#報(bào)錯(cuò)，第一行'a'錯(cuò)誤。
ERROR 1366 (HY000): Incorrect integer value: 'a' for column 'value' at row 1

create table test(value date);
SET sql_mode='';
insert into test(value) values('2020-00-00'); #結(jié)果為 '2020-00-00'
?
SET sql_mode='NO_ZERO_IN_DATE';
insert into test(value) values('2021-00-00'); #不符合,轉(zhuǎn)為 '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');
# 報(bào)錯(cuò)終止
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'value' at row 1

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);?
#報(bào)錯(cuò)：ERROR 1365 (22012): Division by 0

SET sql_mode='';
grant all on test.* to test01@'localhost'; ?#不報(bào)錯(cuò)（無需要設(shè)置密碼）
SET sql_mode='NO_AUTO_CREATE_USER';
# 報(bào)錯(cuò)
ERROR 1133 (42000): Can't find any matching row in the user table

#正確 寫法，需要設(shè)置密碼
grant all on test.* to test01@'localhost' identified by 'test01...';

# 查看當(dāng)前支持的存儲(chǔ)引擎
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'; # 轉(zhuǎn)為默認(rèn)存儲(chǔ)引擎
+------------+--------+
| table_name | engine |
+------------+--------+
| test ? ? ? | InnoDB |
+------------+--------+
SET sql_mode='NO_ENGINE_SUBSTITUTION';
create table test(id int) ENGINE=test;
# 報(bào)錯(cuò)
ERROR 1286 (42000): Unknown storage engine 'test'

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

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