MySQL大批量数据插入，PHP之for不断插入时出现缓慢的解决方案及
很多时候为了测试数据库设计是否恰当，优化SQL语句，需要在表中插入大量的数据，怎么插入大量的数据就是个问题了。 最开始想到的办法就是写一个程序通过一个很大的循环来不停的插入，比如这样： 1 2 3 4 int i = LOOP_COUNT ; while ( i --= 0 ){ //insert d
很多时候为了测试数据库设计是否恰当，优化SQL语句，需要在表中插入大量的数据，怎么插入大量的数据就是个问题了。
最开始想到的办法就是写一个程序通过一个很大的循环来不停的插入，比如这样：
<span>1</span> <span>2</span> <span>3</span> <span>4</span> Copy after login |
<code><span><span>int</span> <span>i</span> <span>=</span> <span>LOOP_COUNT</span><span>;</span>
</span><span><span>while</span><span>(</span><span>i</span><span>-->=</span><span>0</span><span>){</span>
</span><span> <span>//insert data here.</span>
</span><span><span>}</span>
</span></code>Copy after login |
不过我在这么做的时候发现这样插入数据非常的慢，一秒钟插入的数据量还不到100条，于是想到不要一条一条的插入，而是通过
<span>1</span> Copy after login |
<code><span>INSERT</span> <span>INTO</span> <span>TABLE</span> <span>VALUES</span> <span>(),(),(),()...</span> </code> Copy after login |
这样的方式来插入。于是修改程序为：
<span>1</span> <span>2</span> <span>3</span> <span>4</span> 5 6 7 8 9 Copy after login |
<code><span><span>int</span> <span>i</span> <span>=</span> <span>LOOP_COUNT</span><span>;</span>
</span><span><span>StringBuilder</span> <span>stringBuilder</span><span>;</span>
</span><span><span>while</span><span>(</span><span>i</span><span>-->=</span><span>0</span><span>){</span>
</span><span> <span>if</span><span>(</span><span>LOOP_COUNT</span><span>!=</span><span>i</span> <span>&&</span> <span>i</span><span>%</span><span>5000</span><span>==</span><span>0</span><span>){</span>
</span><span> <span>//通过insert values的方式插入这5000条数据并清空stringBuilder</span>
</span><span> <span>}</span>
</span><span> <span>stringBuilder</span><span>.</span><span>append</span><span>(</span><span>"(数据)"</span><span>);</span>
</span><span><span>}</span>
</span><span><span>//插入剩余的数据</span>
</span></code>Copy after login |
这样做的插入速度是上升了很多，不过如果想要插入大量的输入，比如上亿条，那么花费的时间还是非常长的。
查询MySQL的文档，发现了一个页面：LOAD DATA INFILE 光看这个名字，觉得有戏，于是仔细看了下。
官方对于这个命令的描述是：
<span>1</span> <span>2</span> <span>3</span> <span>4</span> 5 6 7 8 9 10 11 12 13 14 15 16 Copy after login |
<code><span><span>LOAD</span> <span>DATA</span> <span>[</span><span>LOW_PRIORITY</span> <span>|</span> <span>CONCURRENT</span><span>]</span> <span>[</span><span>LOCAL</span><span>]</span> <span>INFILE</span> <span>'</span><span>file_name</span><span>'</span>
</span><span> <span>[</span><span>REPLACE</span> <span>|</span> <span>IGNORE</span><span>]</span>
</span><span> <span>INTO</span> <span>TABLE</span> <span>tbl_name</span>
</span><span> <span>[</span><span>CHARACTER</span> <span>SET</span> <span>charset_name</span><span>]</span>
</span><span> <span>[{</span><span>FIELDS</span> <span>|</span> <span>COLUMNS</span><span>}</span>
</span><span> <span>[</span><span>TERMINATED</span> <span>BY</span> <span>'</span><span>string</span><span>'</span><span>]</span>
</span><span> <span>[[</span><span>OPTIONALLY</span><span>]</span> <span>ENCLOSED</span> <span>BY</span> <span>'</span><span>char</span><span>'</span><span>]</span>
</span><span> <span>[</span><span>ESCAPED</span> <span>BY</span> <span>'</span><span>char</span><span>'</span><span>]</span>
</span><span> <span>]</span>
</span><span> <span>[</span><span>LINES</span>
</span><span> <span>[</span><span>STARTING</span> <span>BY</span> <span>'</span><span>string</span><span>'</span><span>]</span>
</span><span> <span>[</span><span>TERMINATED</span> <span>BY</span> <span>'</span><span>string</span><span>'</span><span>]</span>
</span><span> <span>]</span>
</span><span> <span>[</span><span>IGNORE</span> <span>number</span> <span>LINES</span><span>]</span>
</span><span> <span>[(</span><span>col_name_or_user_var</span><span>,...)]</span>
</span><span> <span>[</span><span>SET</span> <span>col_name</span> <span>=</span> <span>expr</span><span>,...]</span>
</span></code>Copy after login |
命令不复杂，具体的每个参数的意义和用法请看官方的解释 http://dev.mysql.com/doc/refman/5.5/en/load-data.html
那么现在做的就是生成数据了，我习惯用\t作为数据的分隔符、用\n作为一行的分隔符，所以生成数据的代码如下：
<span>1</span> <span>2</span> <span>3</span> <span>4</span> 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 Copy after login |
<code><span><span>long</span> <span>start</span> <span>=</span> <span>System</span><span>.</span><span>currentTimeMillis</span><span>()</span> <span>/</span> <span>1000</span><span>;</span>
</span><span><span>try</span> <span>{</span>
</span><span> <span>File</span> <span>file</span> <span>=</span> <span>new</span> <span>File</span><span>(</span><span>FILE</span><span>);</span>
</span><span> <span>if</span> <span>(</span><span>file</span><span>.</span><span>exists</span><span>())</span> <span>{</span>
</span><span> <span>file</span><span>.</span><span>delete</span><span>();</span>
</span><span> <span>}</span>
</span><span> <span>file</span><span>.</span><span>createNewFile</span><span>();</span>
</span><span> <span>FileOutputStream</span> <span>outStream</span> <span>=</span> <span>new</span> <span>FileOutputStream</span><span>(</span><span>file</span><span>,</span> <span>true</span><span>);</span>
</span><span> <span>StringBuilder</span> <span>builder</span> <span>=</span> <span>new</span> <span>StringBuilder</span><span>(</span><span>10240</span><span>);</span>
</span><span> <span>DateFormat</span> <span>dateFormat</span> <span>=</span> <span>new</span> <span>SimpleDateFormat</span><span>(</span><span>DATE_FORMAT</span><span>);</span>
</span><span> <span>Random</span> <span>rand</span> <span>=</span> <span>new</span> <span>Random</span><span>();</span>
</span><span> <span>String</span> <span>tmpDate</span> <span>=</span> <span>dateFormat</span><span>.</span><span>format</span><span>(</span><span>new</span> <span>Date</span><span>());</span>
</span><span> <span>Long</span> <span>tmpTimestamp</span> <span>=</span> <span>System</span><span>.</span><span>currentTimeMillis</span><span>()</span> <span>/</span> <span>1000</span><span>;</span>
</span><span> <span>int</span> <span>i</span> <span>=</span> <span>0</span><span>;</span>
</span><span> <span>while</span> <span>(</span><span>i</span><span>++</span> <span> <span>LOOP</span><span>)</span> <span>{</span>
</span><span> <span>if</span> <span>(</span><span>i</span> <span>></span> <span>0</span> <span>&&</span> <span>i</span> <span>%</span> <span>30000</span> <span>==</span> <span>0</span><span>)</span> <span>{</span>
</span><span> <span>System</span><span>.</span><span>out</span><span>.</span><span>println</span><span>(</span><span>"write offset:"</span> <span>+</span> <span>i</span><span>);</span>
</span><span> <span>outStream</span><span>.</span><span>write</span><span>(</span><span>builder</span><span>.</span><span>toString</span><span>().</span><span>getBytes</span><span>(</span><span>CHARCODE</span><span>));</span>
</span><span> <span>builder</span> <span>=</span> <span>new</span> <span>StringBuilder</span><span>(</span><span>10240</span><span>);</span>
</span><span> <span>}</span>
</span><span> <span>if</span> <span>(</span><span>tmpTimestamp</span><span>.</span><span>compareTo</span><span>(</span><span>System</span><span>.</span><span>currentTimeMillis</span><span>()</span> <span>/</span> <span>1000</span><span>)</span> <span>!=</span> <span>0</span><span>)</span> <span>{</span>
</span><span> <span>tmpDate</span> <span>=</span> <span>dateFormat</span><span>.</span><span>format</span><span>(</span><span>new</span> <span>Date</span><span>());</span>
</span><span> <span>tmpTimestamp</span> <span>=</span> <span>System</span><span>.</span><span>currentTimeMillis</span><span>()</span> <span>/</span> <span>1000</span><span>;</span>
</span><span> <span>}</span>
</span><span> <span>builder</span><span>.</span><span>append</span><span>(</span><span>tmpDate</span><span>);</span>
</span><span> <span>builder</span><span>.</span><span>append</span><span>(</span><span>"\t"</span><span>);</span>
</span><span> <span>builder</span><span>.</span><span>append</span><span>(</span><span>rand</span><span>.</span><span>nextInt</span><span>(</span><span>999</span><span>));</span>
</span><span> <span>builder</span><span>.</span><span>append</span><span>(</span><span>"\t"</span><span>);</span>
</span><span> <span>builder</span><span>.</span><span>append</span><span>(</span><span>Encrypt</span><span>.</span><span>md5</span><span>(</span><span>System</span><span>.</span><span>currentTimeMillis</span><span>()</span> <span>+</span> <span>""</span> <span>+</span> <span>rand</span><span>.</span><span>nextInt</span><span>(</span><span>99999999</span><span>)));</span>
</span><span> <span>builder</span><span>.</span><span>append</span><span>(</span><span>"\t"</span><span>);</span>
</span><span> <span>builder</span><span>.</span><span>append</span><span>(</span><span>rand</span><span>.</span><span>nextInt</span><span>(</span><span>999</span><span>)</span> <span>%</span> <span>2</span> <span>==</span> <span>0</span> <span>?</span> <span>"AA."</span> <span>:</span> <span>"BB"</span><span>);</span>
</span><span> <span>builder</span><span>.</span><span>append</span><span>(</span><span>"\t"</span><span>);</span>
</span><span> <span>builder</span><span>.</span><span>append</span><span>(</span><span>rand</span><span>.</span><span>nextFloat</span><span>()</span> <span>*</span> <span>2000</span><span>);</span>
</span><span> <span>builder</span><span>.</span><span>append</span><span>(</span><span>"\t"</span><span>);</span>
</span><span> <span>builder</span><span>.</span><span>append</span><span>(</span><span>rand</span><span>.</span><span>nextInt</span><span>(</span><span>9</span><span>));</span>
</span><span> <span>builder</span><span>.</span><span>append</span><span>(</span><span>"\n"</span><span>);</span>
</span><span> <span>}</span>
</span><span> <span>System</span><span>.</span><span>out</span><span>.</span><span>println</span><span>(</span><span>"write data:"</span> <span>+</span> <span>i</span><span>);</span>
</span><span> <span>outStream</span><span>.</span><span>write</span><span>(</span><span>builder</span><span>.</span><span>toString</span><span>().</span><span>getBytes</span><span>(</span><span>CHARCODE</span><span>));</span>
</span><span> <span>outStream</span><span>.</span><span>close</span><span>();</span>
</span><span><span>}</span> <span>catch</span> <span>(</span><span>Exception</span> <span>e</span><span>)</span> <span>{</span>
</span><span> <span>e</span><span>.</span><span>printStackTrace</span><span>();</span>
</span><span><span>}</span>
</span><span><span>System</span><span>.</span><span>out</span><span>.</span><span>println</span><span>(</span><span>System</span><span>.</span><span>currentTimeMillis</span><span>()</span> <span>/</span> <span>1000</span> <span>-</span> <span>start</span><span>);</span>
</span></span></code>Copy after login |
这段代码会生成一个数据文件，每一行为一条记录，然后再使用上面提到的 LOAD DATA 来导入数据就可以了，我在公司的电脑下（2G内存+垃圾双核CPU，MySQL直接装在windows下，没任何优化，developer模式）每秒能达到近万条的插入速度，比其他方式都快很多。
另外如果想直接用GUI工具操作也可以，比如SQLYog中，右键要导入的表，选择Import – Import CSV Data Using Load Local.. 然后设置好编码、分隔符后就可以直接导入了。
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1377
52
MySQL: The Ease of Data Management for Beginners
Apr 09, 2025 am 12:07 AM
MySQL is suitable for beginners because it is simple to install, powerful and easy to manage data. 1. Simple installation and configuration, suitable for a variety of operating systems. 2. Support basic operations such as creating databases and tables, inserting, querying, updating and deleting data. 3. Provide advanced functions such as JOIN operations and subqueries. 4. Performance can be improved through indexing, query optimization and table partitioning. 5. Support backup, recovery and security measures to ensure data security and consistency.
MySQL: Simple Concepts for Easy Learning
Apr 10, 2025 am 09:29 AM
MySQL is an open source relational database management system. 1) Create database and tables: Use the CREATEDATABASE and CREATETABLE commands. 2) Basic operations: INSERT, UPDATE, DELETE and SELECT. 3) Advanced operations: JOIN, subquery and transaction processing. 4) Debugging skills: Check syntax, data type and permissions. 5) Optimization suggestions: Use indexes, avoid SELECT* and use transactions.
How to create navicat premium
Apr 09, 2025 am 07:09 AM
Create a database using Navicat Premium: Connect to the database server and enter the connection parameters. Right-click on the server and select Create Database. Enter the name of the new database and the specified character set and collation. Connect to the new database and create the table in the Object Browser. Right-click on the table and select Insert Data to insert the data.
The Future of PHP: Adaptations and Innovations
Apr 11, 2025 am 12:01 AM
The future of PHP will be achieved by adapting to new technology trends and introducing innovative features: 1) Adapting to cloud computing, containerization and microservice architectures, supporting Docker and Kubernetes; 2) introducing JIT compilers and enumeration types to improve performance and data processing efficiency; 3) Continuously optimize performance and promote best practices.
PHP vs. Python: Understanding the Differences
Apr 11, 2025 am 12:15 AM
PHP and Python each have their own advantages, and the choice should be based on project requirements. 1.PHP is suitable for web development, with simple syntax and high execution efficiency. 2. Python is suitable for data science and machine learning, with concise syntax and rich libraries.
MySQL and SQL: Essential Skills for Developers
Apr 10, 2025 am 09:30 AM
MySQL and SQL are essential skills for developers. 1.MySQL is an open source relational database management system, and SQL is the standard language used to manage and operate databases. 2.MySQL supports multiple storage engines through efficient data storage and retrieval functions, and SQL completes complex data operations through simple statements. 3. Examples of usage include basic queries and advanced queries, such as filtering and sorting by condition. 4. Common errors include syntax errors and performance issues, which can be optimized by checking SQL statements and using EXPLAIN commands. 5. Performance optimization techniques include using indexes, avoiding full table scanning, optimizing JOIN operations and improving code readability.
How to create a new connection to mysql in navicat
Apr 09, 2025 am 07:21 AM
You can create a new MySQL connection in Navicat by following the steps: Open the application and select New Connection (Ctrl N). Select "MySQL" as the connection type. Enter the hostname/IP address, port, username, and password. (Optional) Configure advanced options. Save the connection and enter the connection name.
How to execute sql in navicat
Apr 08, 2025 pm 11:42 PM
Steps to perform SQL in Navicat: Connect to the database. Create a SQL Editor window. Write SQL queries or scripts. Click the Run button to execute a query or script. View the results (if the query is executed).
