Shulou(Shulou.com)06/01 Report--

MySQL batch insertion problem

In the development of the project, because there are some basic data of the old system that need to be imported in advance, so I did a batch import operation when importing, but because the size of SQL statements acceptable in MySQL is limited, so although I only have 500 pieces per batch, I still cannot insert them. At this time, the code error is 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.

From the error we can quickly know that the SQL statement packet is too large, we can set the MySQL server parameter max_allowed_packet to solve this problem.

solutions

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

#Find my.cnf file #whereis my.cnf#vim my.cnf----------------------------

2. Max_allowed_packet is set temporarily, via statement

myslq>set global max_allowed_packet = 1024*1024*1024 After restarting this method, max_allowed_packet becomes invalid

By default the Mysql parameter max_allowed_packet value is 1M.

MySQL index case-insensitive issues

When creating indexes in MySQL databases, they are case-insensitive by default, such as 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 ' postcode',`address` varchar(50) DEFAULT NULL COMMENT 'address',`link_man` varchar(15) DEFAULT NULL COMMENT ' contact',`link_phone` varchar(30) DEFAULT NULL COMMENT 'contact',`fax` varchar(30) DEFAULT COMMENT ' fax',`can_accept_car_time` varchar(40) DEFAULT NULL COMMENT 'available time',`type` varchar(1) DEFAULT NULL COMMENT ' category',`maintenance_type` varchar(1) DEFAULT NULL COMMENT 'maintenance type',`brand` varchar(4) DEFAULT COMMENT ' brand', NULL`reservation` varchar(40) DEFAULT NULL COMMENT 'reservation', `enable` int(1) DEFAULT '1',`msg_code` varchar(64) NOT NULL COMMENT',`receive_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT',`create_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT',`modified_on` datetime CURRENT_TIMESTAMP ON DEFAULT CURRENT_TIMESTAMP COMMENT', UPDATE 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 list';

But when I insert the address codes H12C and h22C, respectively, I report an error and throw an exception: Duplicate entry 'H12C' for key 'normal_localization_code', which means case insensitive, so this place needs to solve this problem.

solution

1. Set the field to Binary, then 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 COLATE 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 PHONE', `fax` varchar(30) DEFAULT COMMENT 'FAX',`can_accept_car_time` varchar(40) DEFAULT NULL COMMENT ' AVAILABLE 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-encoding',`receive_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT',`create_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT',`modified_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT', 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 dictionary properties of the original table: ALTER TABLE `location`CHANGE COLUMN `location_code` `location_code` VARCHAR(4) CHARACTER SET 'utf8' BINARY NOT NULL DEFAULT '' ;

The above method is solved.

Case insensitive queries

solution

1. Query statement plus binary

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

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.