Solutions to MySQL batch insert and unique index problems

MySQL batch insert problem

When developing a project, because some basic data of the old system needs to be imported in advance, I did a batch import operation during the import. However, because the acceptable SQL statement size in MySQL is limited, although I can only batch 500 each time, I still cannot insert it. At this time, the code reports the following error:

The 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 can quickly know that the SQL statement data packet is too large. 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 = 3000
max_allowed_packet=1024M

#Save and restart the mysql service to take effect#service mysqld restart

2. Temporarily set max_allowed_packet, set it through the statement

myslq>set global max_allowed_packet = 1024*1024*1024

After restarting in this way, max_allowed_packet becomes invalid

By default, the MySQL parameter max_allowed_packet value is 1M.

MySQL index is not case sensitive

When creating an index in a MySQL database, it is case-insensitive by default. For example, the following statement:

CREATE TABLE `location` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `rc` varchar(2) DEFAULT NULL COMMENT 'R/C',
 `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 'Postal code',
 `address` varchar(50) DEFAULT NULL COMMENT 'Address',
 `link_man` varchar(15) DEFAULT NULL COMMENT 'Contact person',
 `link_phone` varchar(30) DEFAULT NULL COMMENT 'Contact phone number',
 `fax` varchar(30) DEFAULT NULL COMMENT 'Fax',
 `can_accept_car_time` varchar(40) DEFAULT NULL COMMENT 'Available car pick-up 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 '1',
 `msg_code` varchar(64) NOT NULL COMMENT 'Message code',
 `receive_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Receive date',
 `create_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation date',
 `modified_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'modified date',
 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=16325 DEFAULT CHARSET=utf8 COMMENT='Address table';

But when I inserted the address codes H12C and h12C respectively, an error was reported and an exception was thrown: Duplicate entry 'H12C' for key 'normal_localtion_code', which means that it is case-insensitive, so this problem needs to be solved.

Workaround

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

CREATE TABLE `location` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `rc` char(2) DEFAULT NULL COMMENT 'R/C',
 `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 'Postal code',
 `address` varchar(50) DEFAULT NULL COMMENT 'Address',
 `link_man` varchar(16) DEFAULT NULL COMMENT 'Contact person',
 `link_phone` varchar(30) DEFAULT NULL COMMENT 'Contact phone number',
 `fax` varchar(30) DEFAULT NULL COMMENT 'Fax',
 `can_accept_car_time` varchar(40) DEFAULT NULL COMMENT 'Available car pick-up 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 '1',
 `msg_code` varchar(64) NOT NULL COMMENT 'Message code',
 `receive_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Receive date',
 `create_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation date',
 `modified_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'modified date',
 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 original table dictionary attributes:

ALTER TABLE `location`
CHANGE COLUMN `location_code` `location_code` VARCHAR(4) CHARACTER SET 'utf8' BINARY NOT NULL DEFAULT '';

The above method solves the problem.

The query is not case sensitive

Workaround

1. Add binary to query statement

2. Consistent with the index solution, set the field attribute to binary.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.

You may also be interested in:

Examples of 4 methods for inserting large amounts of data in MySQL
MYSQL batch insert data implementation code
Tutorial on implementing batch inserts in MySQL to optimize performance
How to avoid MySQL batch inserts with unique indexes
MySQL batch insert data script
Detailed explanation of MySQL batch SQL insert performance optimization
How to quickly insert 10 million records into MySQL
Detailed explanation of several examples of insert and batch statements in MySQL

<<: Detailed explanation of the implementation principles of call, apply, and bind in JavaScript

>>: How to implement cross-domain API proxy forwarding through Nginx proxy forwarding configuration