[Mysql] Select not, insert error problem, field encoding problem

The Mysql 5.5 table structure is:

CREATE TABLE `account` (  `name` varchar(20) NOT NULL,  PRIMARY KEY (`name`)) ENGINE=InnoDB DEFAULT CHARSET=gbk;

nameThe current encoding of the field:

CharacterSet:gbkCollation:gbk_chinese_ci

At the moment my program logic is:

First of all:

select * from `account` where `name`=?

If NULL is returned, the name is inserted:

insert into `account` (`name`) values(?)

Now in my background there are several error records:

Com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException:Duplicate entry ' 积分 ' for key ' PRIMARY '

(Please ignore name is garbled, this may be caused by my output record text)

My question is, why did I select the name before I would insert and cause this exception?

Does this have to do with the Name field I set for GBK encoding? Are there any reference articles available for me to look at?

Why should the field encoding be set to avoid this problem?

BTW: Using the Ignore parameter can of course ignore the exception, but I would like to know why the previous select was not available and insert was wrong

Reply content:

The Mysql 5.5 table structure is:

CREATE TABLE `account` (  `name` varchar(20) NOT NULL,  PRIMARY KEY (`name`)) ENGINE=InnoDB DEFAULT CHARSET=gbk;

nameThe current encoding of the field:

CharacterSet:gbkCollation:gbk_chinese_ci

At the moment my program logic is:

First of all:

select * from `account` where `name`=?

If NULL is returned, the name is inserted:

insert into `account` (`name`) values(?)

Now in my background there are several error records:

Com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException:Duplicate entry ' 积分 ' for key ' PRIMARY '

(Please ignore name is garbled, this may be caused by my output record text)

My question is, why did I select the name before I would insert and cause this exception?

Does this have to do with the Name field I set for GBK encoding? Are there any reference articles available for me to look at?

Why should the field encoding be set to avoid this problem?

BTW: Using the Ignore parameter can of course ignore the exception, but I would like to know why the previous select was not available and insert was wrong

Character encoding problem please expert to answer.

I'll just say something else.

In the case of high concurrency, the results of your two operations are indeterminate and unsafe.

It is safer to use atomic operations such as the following.

INSERT INTO `table` (`id`, `name`) VALUES (?, ?) ON DUPLICATE KEY UPDATE `name`=VALUES(`name`);

Or

INSERT IGNORE INTO `table` (`id`, `name`) VALUES (?, ?)

Personally, this should not be a coding problem, but a concurrency problem, as @HJin. Me says. That is, session1 after the select to get the empty set; Session2 to insert a name of the same; At this point session1 to insert, you get duplicated.

The default isolation level for MySQL transactions is repeated read, which does not prevent the above situation from being tested.

session1:  start transaction;session2:  start transaction;session1:  select * from account where name='11'; //empty setsession2:  insert into account values('11');session2:  commit;session1:  insert into account values('11'); // duplicate key

