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 control the proportion of Flex child elements on the main axis

background Flex layout achieves alignment and spa...

JavaScript to achieve digital clock effects

This article example shares the specific code for...

Reasons and solutions for prompting to save action after uploading files in form

The json data must be returned in html format That...

CSS new feature contain controls page redrawing and rearrangement issues

Before introducing the new CSS property contain, ...

How to quickly repair corrupted MySQL database files using the myisamchk and mysqlcheck tools

Because the server's database hard disk space...

After submitting the html drop-down menu, the selected value is retained instead of returning to the default value

Copy code The code is as follows: <html> &l...

Docker setting windows storage path operation

When installing Docker on Windows 10, after selec...

MySQL 8.0.15 installation and configuration graphic tutorial

This article records the installation and configu...

MySQL database must know sql statements (enhanced version)

This is an enhanced version. The questions and SQ...

Two simple menu navigation bar examples

Menu bar example 1: Copy code The code is as foll...

Detailed explanation of adding dotted lines to Vue element tree controls

Table of contents 1. Achieve results 2. Implement...

How to reset password after forgetting password in MySQL8 (MySQL old method doesn't work)

The command line mysqld –skip-grant-tables cannot...

How to install ElasticSearch on Docker in one article

Table of contents Preface 1. Install Docker 2. In...

Limit HTML text box input to only numbers and decimal points

Code: <input type="text" class="...

A few steps to easily build a Windows SSH server

The SSH mentioned here is called Security Shell. ...