Solutions to MySQL batch insert and unique index problems

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

Recommend

How to shrink the log file in MYSQL SERVER

The transaction log records the operations on the...

Solve mysql: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO/YES)

1. Problem Sometimes when we log in to Mysql and ...

js basic syntax and maven project configuration tutorial case

Table of contents 1. js statement Second, js arra...

Detailed explanation of Tomcat's Server Options

1. Configuration By default, the first two are no...

How to manually install MySQL 5.7 on CentOS 7.4

MySQL database is widely used, especially for JAV...

Tips for List Building for Website Maintenance Pages

And, many times, maintenance requires your website...

Mysql multiplication and division precision inconsistency problem (four decimal places after division)

question When I was writing a project function to...

Nginx operation and maintenance domain name verification method example

When configuring the interface domain name, each ...

WeChat applet development practical skills: data transmission and storage

Combining the various problems I encountered in m...

Detailed explanation of MySQL 8.0.18 commands

Open the folder C:\web\mysql-8.0.11 that you just...

MySQL 5.7.20 installation and configuration method graphic tutorial under Windows

Install MySQL under Windows for your reference. T...

How to solve the problem of left alignment of the last line in flex layout space-between

First look at the code and effect↓ <style> ...

Monitor changes in MySQL table content and enable MySQL binlog

Preface binlog is a binary log file, which record...

Basic operations of mysql learning notes table

Create Table create table table name create table...

Implement a simple search engine based on MySQL

Table of contents Implementing a search engine ba...