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

A method of hiding processes under Linux and the pitfalls encountered

Preface 1. The tools used in this article can be ...

Detailed explanation of Windows time server configuration method

Recently, I found that the company's server t...

A brief discussion on several advantages of Vue3

Table of contents 1. Source code 1.1 Monorepo 1.2...

Detailed explanation of the difference and application of CSS3 filter:drop-shadow filter and box-shadow

To use standard CSS3 to achieve the shadow effect...

Vue project @change multiple parameters to pass multiple events

First, there is only one change event. changeleve...

Detailed explanation of Linux zabbix agent deployment and configuration methods

1. Install zabbix-agent on web01 Deploy zabbix wa...

Analysis of Difficulties in Hot Standby of MySQL Database

I have previously introduced to you the configura...

vmware virtual machine ubuntu18.04 installation tutorial

Installation Steps 1. Create a virtual machine 2....

Quickly solve the white gap problem (flash screen) when CSS uses @keyframes to load images for the first cycle

Problem explanation: When using the CSS animation...

How to wrap HTML title attribute

When I was writing a program a few days ago, I wan...

Detailed explanation of MySQL master-slave replication and read-write separation

Table of contents Preface 1. Overview 2. Read-wri...

HTML web page image tag

Insert image tag <IMG> The colorful web page...

IDEA reports an error when connecting to MySQL! Server returns invalid timezone. Go to tab and set serverTimezone property

The road ahead is always so difficult and full of...

Div nested html without iframe

Recently, when doing homework, I needed to nest a ...

Vue implements a simple calculator

This article example shares the specific code of ...