Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

How to compare the efficiency of MySQL batch insert

2025-04-06 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)05/31 Report--

This article introduces you how to compare the efficiency of MySQL batch insert, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Examples of MySQL batch insert efficiency comparison are as follows: [root@BDMYSQL-200-104dumpdir] # more phone_area_new.sql INSERT INTO `phone_area_ new`VALUES ('1302357',' Jiangsu', 'Jiangsu-Nantong', 'Nantong'); INSERT INTO `phone_area_ new`VALUES ('1302358',' Jiangsu', 'Jiangsu-Nantong', 'Nantong') INSERT INTO `phone_area_ New`VALUES ('1302359,' Jiangsu', 'Jiangsu-Nantong', 'Nantong'); INSERT INTO `phone_area_ new`VALUES ('1302360,' Zhejiang', 'Zhejiang Hangzhou', 'Hangzhou'); INSERT INTO `phone_area_ new`VALUES ('1302361,' Zhejiang', 'Zhejiang Hangzhou', 'Hangzhou') INSERT INTO `phone_area_ New`VALUES ('1302362areas,' Zhejiang', 'Zhejiang Hangzhou', 'Hangzhou'); INSERT INTO `phone_area_ new`VALUES ('1302363333,' Zhejiang', 'Zhejiang Hangzhou', 'Hangzhou'); INSERT INTO `phone_area_ new`VALUES ('1302364provinces,' Zhejiang', 'Zhejiang Hangzhou', 'Hangzhou') INSERT INTO `phone_area_ New`VALUES ('1302365,' Zhejiang', 'Zhejiang Hangzhou', 'Hangzhou'); INSERT INTO `phone_area_ new`VALUES ('1302366', 'Zhejiang', 'Zhejiang Hangzhou', 'Hangzhou'); INSERT INTO `phone_area_ new`VALUES ('13017433,Guizhou-Guiyang', 'Guiyang') INSERT INTO `phone_area_ New`VALUES ('1301744th,' Guizhou Province', 'Guizhou-Anshun', 'Anshun'); INSERT INTO `phone_area_ new`VALUES ('1301745,Guizhou', 'Guizhou-Guiyang', 'Guiyang'); INSERT INTO `phone_area_ new`VALUES ('1301746th,' Guizhou Province', 'Guizhou-Guiyang', 'Guiyang') INSERT INTO `phone_area_ new`VALUES ('13017479',' Guizhou-Guiyang', 'Guizhou-Guiyang'); INSERT INTO `phone_area_ new`VALUES ('1301748',' Guizhou', 'Guizhou-Guiyang', 'Guiyang'); INSERT INTO `phone_area_ new`VALUES ('1301749th,' Guizhou Province', 'Guizhou-Zunyi', 'Zunyi') INSERT INTO `phone_area_ New`VALUES ('1301750mm,' Henan', 'Henan-Jiaozuo', 'Jiaozuo');-- the number of More-- (0%) is about 271255 lines: [root@BDMYSQL-200-104dumpdir] # wc-l phone_area_new.sql271255 phone_area_new.sql if you don't merge insert, it will take about three hours to import, which is tragic. Now let's compare the number of mergers.

1. Merging 1000 pieces of data first carries on the text processing.

[root@BDMYSQL-200-104dumpdir] # sed': a Tantra Nathantra;\ r\?\ n [^ (] * /, /; 0, 000, 000ba' phone_area_new.sql > mm.sql

Clear the data in the table

System@localhost 21:01: [netdata] > truncate table phone_area_new;Query OK, 0 rows affected (0.20 sec)

Test imported data

[root@BDMYSQL-200-104dumpdir] # time mysql netdata truncate table phone_area_new;Query OK, 0 rows affected (0.20sec)

Merge data

[root@BDMYSQL-200-104dumpdir] # sed': a Tantra Nathantra;\ r\?\ n [^ (] * /, /; 5000RMB ba'phone_area_new.sql > mm.sql)

Test imported data

[root@BDMYSQL-200-104 dumpdir] # time mysql netdata truncate table phone_area_new;Query OK, 0 rows affected (0.24 sec)

Merge data

[root@BDMYSQL-200-104dumpdir] # sed': a Tantra Nathantra;\ r\?\ n [^ (] * /, /; 0700 thousand trees ba'phone_area_new.sql > mm.sql)

Note it took more than ten seconds to import data to merge here.

[root@BDMYSQL-200-104dumpdir] # time mysql netdata truncate table phone_area_new;Query OK, 0 rows affected (0.20sec)

Merge data

[root@BDMYSQL-200-104dumpdir] # sed': a Tantra Nathantra;\ r\?\ n [^ (] * /, /; 08000RMB ba'phone_area_new.sql > mm.sql

Import data

[root@BDMYSQL-200-104 dumpdir] # time mysql netdata

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report