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

This article mainly introduces how to solve the MySQL batch insertion and unique index problems, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

MySQL bulk insert problem

When developing a project, I did a batch import operation because there were some basic data of the old system that needed to be imported in advance, but because the size of an acceptable SQL statement in MySQL was limited, I was still unable to insert it at a time, although there were only 500items in each batch. At this time, the code reported an error as follows:

Nested exception is com.mysql.jdbc.PacketTooBigException: Packet for query is too large (5677854 > 1048576).

You can change this value on the server by setting the max_allowed_packet' variable.

According to the error report, we will soon know that the SQL statement packet is too large, and we can set the MySQL server parameter max_allowed_packet to solve this problem.

Solution.

1. Add the max_allowed_packet parameter under [mysqld], and set it as large as possible.

# find the my.cnf file # whereis my.cnf#vim my.cnf-- [mysqld] max_connections = 3000max_allowed_packet=1024M# and restart the mysql service to take effect # service mysqld restart

two。 Temporarily set max_allowed_packet, set by statement

Myslq > set global max_allowed_packet = 1024 "1024" 1024

After this method is rebooted, the max_allowed_packet will fail.

By default, the Mysql parameter max_allowed_ packet value is 1m.

MySQL index is not case-sensitive

When you create an index in a MySQL database, it is case-insensitive by default, such as the following statement:

CREATE TABLE `location` (`id`location` (11) NOT NULL AUTO_INCREMENT, `rc` varchar (2) DEFAULT NULL COMMENT'R'Accord Category, `location_ code` varchar (4) DEFAULT NULL COMMENT 'location code', `location_ name`varchar (30) DEFAULT NULL COMMENT 'location name', `zip_ code` varchar (6) DEFAULT NULL COMMENT 'zip code', `address`varchar (50) DEFAULT NULL COMMENT 'address', `link_ man` varchar (15) DEFAULT NULL COMMENT 'contact', `link_ phone` varchar (30) DEFAULT NULL COMMENT 'contact number' `fax` varchar (30) DEFAULT NULL COMMENT 'fax', `can_accept_car_ time` varchar (40) DEFAULT NULL COMMENT 'pickup time', `type` varchar (1) DEFAULT NULL COMMENT 'category', `maintenance_ type` varchar (1) DEFAULT NULL COMMENT 'maintenance type', `brand`varchar (4) DEFAULT NULL COMMENT 'brand', `reservation` varchar (40) DEFAULT NULL COMMENT 'reservation', `enable`int (1) DEFAULT '1percent, `msg_ code` varchar (64) NOT NULL COMMENT' message coding' `receive_ on`date received', `create_ on`date created', `modified_ on`date modified', PRIMARY KEY (`id`), UNIQUE KEY `unique_msg_ code` (`msg_ code`) USING BTREE, UNIQUE KEY `unique_location_ code` (`location_ code`) ENGINE=InnoDB AUTO_INCREMENT=16325 DEFAULT CHARSET=utf8 COMMENT=' address table'

But when I insert address codes H12C and H22C respectively, I report an error and throw an exception: Duplicate entry 'H12C' for key 'normal_localtion_code', means that it is not case-sensitive, so this place needs to solve this problem.

Solution method

1. Set the field to Binary so that the index is case-sensitive.

CREATE TABLE `location` (`id`location` (11) NOT NULL AUTO_INCREMENT, `rc` char (2) DEFAULT NULL COMMENT'R'Accord Category, `location_ code` varchar (4) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT 'location code', `location_ name`varchar (26) DEFAULT NULL COMMENT 'location name', `zip_ code` varchar (6) DEFAULT NULL COMMENT 'zip code', `address`varchar (50) DEFAULT NULL COMMENT 'address', `link_ man` varchar (16) DEFAULT NULL COMMENT 'contact', `link_ phone` varchar (30) DEFAULT NULL COMMENT 'contact number' `fax` varchar (30) DEFAULT NULL COMMENT 'fax', `can_accept_car_ time` varchar (40) DEFAULT NULL COMMENT 'pickup time', `type` varchar (1) DEFAULT NULL COMMENT 'category', `maintenance_ type` varchar (1) DEFAULT NULL COMMENT 'maintenance type', `brand`varchar (4) DEFAULT NULL COMMENT 'brand', `reservation` varchar (40) DEFAULT NULL COMMENT 'reservation', `enable`int (1) DEFAULT '1percent, `msg_ code` varchar (64) NOT NULL COMMENT' message coding' `receive_ on`date received', `create_ on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation date', `modified_ on`date modified', PRIMARY KEY (`id`), UNIQUE KEY `unique_msg_ code` (`msg_ code`) USING BTREE, UNIQUE KEY `unique_location_ code` (`location_ code` USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=4092 DEFAULT CHARSET=utf8 COMMENT=' location table' / / modify the attributes of the original table dictionary: ALTER TABLE `location`CHANGE COLUMN `location_ code``location_ code` VARCHAR (4) CHARACTER SET 'utf8' BINARY NOT NULL DEFAULT''

The above method is solved.

Case insensitivity when querying

Solution method

1. Query statement plus binary

two。 Consistent with the indexing solution, set the field property to binary.

Thank you for reading this article carefully. I hope the article "how to solve the problem of bulk insertion and unique index of MySQL" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.