Bagaimana untuk menetapkan SQL_MODE dalam MySQL 5.7

sql_mode ialah pembolehubah yang mudah diabaikan Nilai lalai dalam 5.5 adalah batal Di bawah tetapan ini, beberapa operasi haram boleh dibenarkan, seperti membenarkan pemasukan beberapa data haram.

Tetapan nilai ini telah diperkukuh dalam 5.6, dan 5.7 memberi lebih perhatian kepada peraturan keselamatan nilai ini lalai kepada mod ketat

1 >

Dengan menetapkan mod sql, pengesahan data dengan tahap ketegasan yang berbeza boleh diselesaikan, dengan berkesan memastikan kesediaan data.

Dengan menetapkan mod sql kepada mod santai, ia dipastikan bahawa kebanyakan sql mematuhi sintaks sql standard Dengan cara ini, apabila aplikasi dipindahkan antara pangkalan data yang berbeza, tidak perlu membuat pengubahsuaian besar sql perniagaan, dan ia boleh dengan mudah Berhijrah ke pangkalan data sasaran.

2. Penerangan tentang nilai lalai parameter sql_mode dalam MySQL5.7 (berikut ialah versi MySQL 5.7.27)

• ONLY_FULL_GROUP_BY

Untuk SQL yang menggunakan GROUP BY untuk membuat pertanyaan, medan yang tidak muncul dalam GROUP BY tidak dibenarkan muncul dalam bahagian SELECT Iaitu medan dalam pertanyaan SELECT mesti muncul dalam GROUP BY atau gunakan fungsi agregat atau mempunyai atribut unik.

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

Pilihan ini hanya berfungsi untuk enjin storan transaksi dan tidak sah untuk enjin storan bukan transaksi Fungsinya ialah Dayakan mod SQL yang ketat. Dalam mod sql yang ketat, jika nilai medan yang tidak memenuhi keperluan dimasukkan atau dikemas kini dalam pernyataan INSERT atau UPDATE, ralat akan dilaporkan terus untuk mengganggu operasi

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

Nilai medan masa yang dimasukkan dalam MySQL tidak membenarkan tarikh dan bulan menjadi sifar

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

Nilai medan masa yang dimasukkan dalam MySQL tidak dibenarkan untuk memasukkan tarikh "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

Dalam pernyataan INSERT atau UPDATE, jika data dibahagikan dengan 0, amaran (dalam mod SQL tidak ketat) atau ralat (dalam mod SQL ketat) akan muncul.

• Apabila pilihan ini dimatikan, nombor dibahagikan dengan 0, menghasilkan NULL dan tiada amaran dijana

• Apabila pilihan ini dihidupkan dan dalam tidak ketat Dalam mod sql, nombor dibahagikan dengan 0, dan NULL diperoleh, tetapi amaran akan dijana

• Apabila pilihan ini dihidupkan dan dalam keadaan ketat mod sql, nombor dibahagikan dengan 0, ralat akan dihasilkan dan operasi akan terganggu

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

Larang GERAN daripada mencipta pengguna dengan kata laluan kosong

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

Apabila menggunakan sintaks CREATE TABLE atau ALTER TABLE untuk jalankan enjin storan, jika enjin storan yang ditetapkan dilumpuhkan atau tidak disusun, ralat akan berlaku.

# 查看当前支持的存储引擎
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'

3. sql_mode tetapan dan pengubahsuaian

Kaedah 1: Ini ialah pembolehubah global yang boleh diubah suai

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

Kaedah 2: Dengan mengubah suai fail konfigurasi (memerlukan mulakan semula untuk berkuat kuasa)

rreeee

Atas ialah kandungan terperinci Bagaimana untuk menetapkan SQL_MODE dalam MySQL 5.7. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!