search
HomeDatabaseMysql TutorialMySQL如何快速插入大量数据_MySQL

bitsCN.com

MySQL如何快速插入大量数据

 

这几天尝试了使用不同的存储引擎大量插入MySQL表数据,主要试验了MyISAM存储引擎和InnoDB。
下面是实验过程:    

一、InnoDB存储引擎。

创建数据库和表

Sql代码  

CREATE DATABASE ecommerce;  

CREATE TABLE employees (       

    id INT NOT NULL,       

    fname VARCHAR(30),       

    lname VARCHAR(30),    

    birth TIMESTAMP,         

    hired DATE NOT NULL DEFAULT '1970-01-01',       

    separated DATE NOT NULL DEFAULT '9999-12-31',       

    job_code INT NOT NULL,       

    store_id INT NOT NULL      

)   

         

partition BY RANGE (store_id) (       

    partition p0 VALUES LESS THAN (10000),       

    partition p1 VALUES LESS THAN (50000),       

    partition p2 VALUES LESS THAN (100000),       

    partition p3 VALUES LESS THAN (150000),  

    Partition p4 VALUES LESS THAN MAXVALUE       

);  

     

创建存储过程

Sql代码  

use ecommerce;  

CREATE PROCEDURE BatchInsert(IN init INT, IN loop_time INT)  

BEGIN  

    DECLARE Var INT;  

    DECLARE ID INT;  

    SET Var = 0;  

    SET ID = init;  

        WHILE Var

        insert into employees(id,fname,lname,birth,hired,separated,job_code,store_id) values(ID,CONCAT('chen',ID),CONCAT('haixiang',ID),Now(),Now(),Now(),1,ID);  

        SET ID = ID + 1;  

        SET Var = Var + 1;  

        END WHILE;  

END;  

 

调用存储过程插入数据

Sql代码  

CALL BatchInsert(30036,200000)  

用时:3h 37min 8sec

二、MyISAM存储引擎

创建表

Sql代码  

use ecommerce;  

CREATE TABLE ecommerce.customer (  

   id INT NOT NULL,  

   email VARCHAR(64) NOT NULL,  

   name VARCHAR(32) NOT NULL,  

   password VARCHAR(32) NOT NULL,  

   phone VARCHAR(13),  

   birth DATE,  

   sex INT(1),  

   avatar BLOB,  

   address VARCHAR(64),  

   regtime DATETIME,  

   lastip VARCHAR(15),  

   modifytime TIMESTAMP NOT NULL,  

  PRIMARY KEY (id)  

)ENGINE = MyISAM ROW_FORMAT = DEFAULT  

partition BY RANGE (id) (       

    partition p0 VALUES LESS THAN (100000),       

    partition p1 VALUES LESS THAN (500000),       

    partition p2 VALUES LESS THAN (1000000),       

    partition p3 VALUES LESS THAN (1500000),  

    partition p4 VALUES LESS THAN (2000000),  

    Partition p5 VALUES LESS THAN MAXVALUE       

);  

 

创建存储过程

Sql代码  

use ecommerce;  

DROP PROCEDURE IF EXISTS ecommerce.BatchInsertCustomer;  

CREATE PROCEDURE BatchInsertCustomer(IN start INT,IN loop_time INT)  

BEGIN  

    DECLARE Var INT;  

    DECLARE ID INT;  

    SET Var = 0;  

    SET ID= start;  

        WHILE Var

        DO  

        insert into customer(ID,email,name,password,phone,birth,sex,avatar,address,regtime,lastip,modifytime)   

        values(ID,CONCAT(ID,'@sina.com'),CONCAT('name_',rand(ID)*10000 mod 200),123456,13800000000,adddate('1995-01-01',(rand(ID)*36520) mod 3652),Var%2,'http://t3.baidu.com/it/u=2267714161,58787848&fm=52&gp=0.jpg','北京市海淀区',adddate('1995-01-01',(rand(ID)*36520) mod 3652),'8.8.8.8',adddate('1995-01-01',(rand(ID)
*36520) mod 3652));  

        SET Var = Var + 1;  

        SET ID= ID + 1;  

        END WHILE;  

END;  

 

调用存储过程插入数据

Sql代码  

ALTER  TABLE  customer  DISABLE  KEYS;   

CALL BatchInsertCustomer(1,2000000);  

ALTER  TABLE  customer  ENABLE  KEYS;  

 

用时:8min 50sec

通过以上对比发现对于插入大量数据时可以使用MyISAM存储引擎,如果再需要修改MySQL存储
引擎可以使用命令:

Sql代码  

ALTER TABLE t ENGINE = MYISAM;  

bitsCN.com
Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
How to sort and rank data in MySQLHow to sort and rank data in MySQLApr 29, 2025 pm 03:48 PM

In MySQL, sorting uses the ORDERBY clause, and ranking uses the RANK(), DENSE_RANK(), and ROW_NUMBER() functions. 1. Sort: Use ORDERBY clause, such as SELECT*FROMemployeesORDERBYsalaryDESC; 2. Ranking: Use window functions, such as SELECTemployee_name, salary, RANK()OVER(ORDERBYsalaryDESC)ASrankFROMemployees; these operations are based on SQL query optimizer and execution engine, and are often used to sort quickly or merge sort, and ranking depends on window function calculation.

Creation and calling methods of MySQL stored proceduresCreation and calling methods of MySQL stored proceduresApr 29, 2025 pm 03:45 PM

To create and call stored procedures in MySQL, follow the following steps: 1. Create stored procedures: Use the CREATEPROCEDURE statement to define stored procedures, including names, parameters, and SQL statements. 2. Compile stored procedures: MySQL compiles stored procedures into executable code and stores them. 3. Call stored procedure: use CALL statement and pass parameters. 4. Execute stored procedures: MySQL executes the SQL statements in it, processes parameters and returns the result.

How to set up MySQL service automatically startsHow to set up MySQL service automatically startsApr 29, 2025 pm 03:42 PM

The MySQL service can be set to automatically start on Windows, Linux, and macOS. 1) On Windows, use the command "scconfigmysqlstart=auto" to configure. 2) On Linux, enable it using "sudosystemctlenablemysql". 3) On macOS, create and load the launchd configuration file to achieve automatic startup.

How to view detailed structure information of MySQL tablesHow to view detailed structure information of MySQL tablesApr 29, 2025 pm 03:39 PM

The methods to view the MySQL table structure include: 1. Use the DESCRIBE command to view column information; 2. Use the SHOWCREATETABLE command to view table creation statements; 3. Use information_schema to query more detailed information. These methods help to quickly understand table structure and improve work efficiency.

Detailed explanation of the installation steps of MySQL on macOS systemDetailed explanation of the installation steps of MySQL on macOS systemApr 29, 2025 pm 03:36 PM

Installing MySQL on macOS can be achieved through the following steps: 1. Install Homebrew, using the command /bin/bash-c"$(curl-fsSLhttps://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)". 2. Update Homebrew and use brewupdate. 3. Install MySQL and use brewinstallmysql. 4. Start MySQL service and use brewservicesstartmysql. After installation, you can use mysql-u

How to use conditional filtering and grouping in MySQL queryHow to use conditional filtering and grouping in MySQL queryApr 29, 2025 pm 03:33 PM

In MySQL, conditional filtering is implemented through the WHERE clause and grouping is completed through the GROUPBY clause. 1. Use the WHERE clause to filter data, such as finding employees with salary above 5,000. 2. Use the GROUPBY clause to group and aggregate data, such as counting the number of employees by department. 3. Choose the appropriate index to optimize query performance and avoid using functions or expressions as WHERE conditions. 4. Combining subqueries and EXPLAIN commands improve the efficiency of complex queries.

How to clear MySQL table data but preserve table structureHow to clear MySQL table data but preserve table structureApr 29, 2025 pm 03:30 PM

In MySQL, clearing table data but preserving table structure can be implemented through the TRUNCATETABLE and DELETE commands. 1. The TRUNCATETABLE command quickly deletes all records and resets the self-increment column. 2. The DELETE command deletes data line by line, does not reset the self-increment column, and can delete specific records in combination with the WHERE clause.

Methods to deduplicate MySQL query resultsMethods to deduplicate MySQL query resultsApr 29, 2025 pm 03:27 PM

Deduplication in MySQL mainly uses DISTINCT and GROUPBY. 1.DISTINCT is used to return unique values, such as SELECTDISTINCTname, ageFROMusers. 2. GROUPBY realizes deduplication through grouping and can perform aggregation operations, such as SELECTid, name, MAX(created_at)aslatest_dateFROMusersGROUPBYname.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Atom editor mac version download

Atom editor mac version download

The most popular open source editor

DVWA

DVWA

Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

VSCode Windows 64-bit Download

VSCode Windows 64-bit Download

A free and powerful IDE editor launched by Microsoft

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment