Shulou(Shulou.com)05/31 Report--

Insert 0 records lead to data confusion, how to do, in response to this problem, this article details the corresponding analysis and solution, hoping to help more friends who want to solve this problem to find a simpler and easier way.

Environment.MySQL 5.6.14

SQL_Mode:STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

Production environment configuration table. Set PK to self-increment.

One day a classmate asked me to help guide a batch of data from test to production.

I hate this way, but there is no way.. I'm gonna have to do it.

The day after the import, the business found that a lot of production data was out of order.

This gift configuration table, the primary key was originally designed to be self-increasing primary key.

But then they used zero for a special gift... The pit is here.

process simulation

drop table if exists config_gift;

create table config_gift(

GiftID int not null primary key auto_increment,

GiftName varchar(32) not null

) auto_increment=50000;

insert into config_gift(GiftName) select 'flowers';

insert into config_gift(GiftName) select 'firecracker';

insert into config_gift(GiftName) select '';

select * from config_gift;

SQL_Mode at the time was:STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

If the following statement is executed,

insert into config_gift select 0,'cake';

insert into config_gift select null,'fragrance';

The results are as follows:

MySQL If you have set the primary key to auto-increment, but insert 0 or null as the primary key value later, MySQL will replace the original 0 or null with the auto-increment value.

Business code written dead 0 this gift ID judgment, so led to a lot of data disorder, took a long time to correct.

It's a surprise.

This special gift is designated by 0, and no one notifies the database...

Data on-line, are a batch of data, human discrimination data seems unrealistic.

SQL_mode is safe.

Under self-increasing primary key, process data with primary key 0

set @@session.sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,no_auto_value_on_zero'

After modifying SQL_mode at the Global and Session levels, gifts with primary key 0 are inserted correctly.

The main note is that even in this SQL_mode (STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,no_auto_value_on_zero)

If the primary key is null, the value of the primary key will still be used as the primary key instead of reporting an error.

About Insert 0 records lead to data confusion, the answer to the question is shared here, I hope the above content can be of some help to everyone, if you still have a lot of doubts, you can pay attention to the industry information channel to learn more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.