MySQL insert statement usage and optimization tutorial, mysqlinsert

Last Update:2016-03-06 Source: Internet

Author: User

Tags mysql insert

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

MySQL insert statement usage and optimization tutorial, mysqlinsert

MySQL tables use the insert into SQL statement to INSERT data.
You can use the mysql> Command Prompt window to insert data into the data table, or use the PHP script to insert data.
Syntax
The general insert into SQL syntax for inserting data INTO a MySQL DATA table is as follows:

INSERT INTO table_name ( field1, field2,...fieldN )            VALUES            ( value1, value2,...valueN );

If the data type is dense, single quotes or double quotation marks must be used, for example, "value ".
Insert data in the Command Prompt window
We will use the SQL INSERT INTO statement to INSERT data to the MySQL Data Table runoob_tbl.
Instance
In the following example, we want to insert three pieces of data into the runoob_tbl table:

root@host# mysql -u root -p password;Enter password:*******mysql> use RUNOOB;Database changedmysql> INSERT INTO runoob_tbl    ->(runoob_title, runoob_author, submission_date)   ->VALUES   ->("Learn PHP", "John Poul", NOW());Query OK, 1 row affected (0.01 sec)mysql> INSERT INTO runoob_tbl   ->(runoob_title, runoob_author, submission_date)   ->VALUES   ->("Learn MySQL", "Abdul S", NOW());Query OK, 1 row affected (0.01 sec)mysql> INSERT INTO runoob_tbl   ->(runoob_title, runoob_author, submission_date)   ->VALUES   ->("JAVA Tutorial", "Sanjay", '2007-05-06');Query OK, 1 row affected (0.01 sec)mysql>

Note: The arrow mark (->) is not part of an SQL statement. It only indicates a new line. If an SQL statement is too long, you can use the Enter key to create a new line to write SQL statements. The command terminator of the SQL statement is a semicolon (;).
In the above instance, we did not provide runoob_id data, because this field has been set to AUTO_INCREMENT (automatically added) attribute during table creation. Therefore, this field will automatically increase without setting it. In the instance, NOW () is a MySQL function that returns the date and time.
Use a PHP script to insert data
You can use the mysql_query () function of PHP to execute the SQL INSERT INTO command to INSERT data.
This function has two parameters. TRUE is returned when execution is successful. Otherwise, FALSE is returned.
Syntax

bool mysql_query( sql, connection );

Parameters:
SQL: required. It specifies the SQL query to be sent. Note that the query string should not end with a semicolon.
Connection: (optional) specifies the SQL connection identifier. If not specified, the last opened connection is used.
Instance
In the following example, the program receives the three fields entered by the user and inserts them into the data table:

<Html> 
When receiving data submitted by users, we need to use the get_magic_quotes_gpc () function to determine whether the escape of special characters is enabled for data security. If this option is off (not enabled) and 0 is returned, we must call the addslashes function to add escape characters to the string.
.
You can also add other methods to check data, such as email format verification, phone number verification, and integer verification.

Optimize insert Performance
Mysql insert statement syntax
insert into `table`(`field1`,`field2`) values('value1','value2'); 
How to Improve insert Performance
1. Insert multiple data records into one SQL statement
INSERT INTO `insert_table` (`uid`, `content`, `type`) VALUES ('userid_0', 'content_0', 0); INSERT INTO `insert_table` (`uid`, `content`, `type`) VALUES ('userid_1', 'content_1', 1); 
Can be written
Copy codeThe Code is as follows:
Insert into 'insert _ table' ('uid', 'content', 'type') VALUES ('userid _ 0', 'content _ 0', 0 ), ('userid _ 1', 'content _ 1', 1 );

2. Use transactions
START TRANSACTION; INSERT INTO `insert_table` (`uid`, `content`, `type`) VALUES ('userid_0', 'content_0', 0); INSERT INTO `insert_table` (`uid`, `content`, `type`) VALUES ('userid_1', 'content_1', 1); ... COMMIT; 
Note:
1. The length of an SQL statement is limited. Pay attention to the SQL statement merging. The length limit can be modified using the max_allowed_packet configuration item. The default value is 1 MB.
2. Too many transactions will affect execution efficiency. mysql has the innodb_log_buffer_size configuration item. exceeding this value will use disk data and affect execution efficiency.
Description of transaction configuration items:
1. innodb_buffer_pool_size
If Innodb is used, this is an important variable. Compared with MyISAM, Innodb is more sensitive to buffer size. MySIAM may also use the default key_buffer_size for large data volumes, but Innodb will feel crawling when using the default value for large data volumes. The Innodb buffer pool caches data and indexes, so you do not need to leave space for the system cache. If you only use Innodb, you can set this value to 70%-80% of the memory. Similar to key_buffer, if the data volume is small and does not increase much, do not set this value too high or increase the memory usage.
2. innodb_additional_pool_size
This effect is not very obvious, at least when the operating system can reasonably allocate memory. However, you may still need to set it to 20 MB or more to see how much memory Innodb will allocate for other purposes.
3. innodb_log_file_size
It is very important to write a lot, especially when there is a large amount of data. Note that large files provide higher performance, but it takes more time to recover the database. I usually use 64 M-512 M, depending on the server space.
4. innodb_log_buffer_size
The default value can be used for most moderate write operations and short transactions. If you often update or use a lot of blob data, you should increase this value. But it is a waste of memory because it will always flush in one second ?) Once, so you do not need to set it to more than 1 second. 8-16 m should be enough. A smaller application can be used.
5. innodb_flush_log_at_trx_commit
Why is Innodb 100 times slower than MyISAM? You probably forgot to adjust this value. The default value 1 indicates that logs need to be written to the hard disk (flush) for each transaction commit or non-transactional command. This is time-consuming. Especially when Battery backed up cache is used. Set to 2 is applicable to many applications, especially the conversion from the MyISAM table. It means writing data to the system cache instead of writing data to the hard disk. Logs are flushed to hard disks per second, so you will not lose updates that exceed 1-2 seconds. Setting 0 is faster, but the security is poor. Even if MySQL fails, the transaction data may be lost. Value 2 can only lose data when the entire operating system is down.
Articles you may be interested in:

 
 
  Analysis on Insert Into data insertion usage in PHP + MySQL
 
  Use the MySQL insert into statement correctly
 
  Tips for MySql insert operations
 
  Gradually analyze the reasons why MySQL slave database com_insert does not change
 
  MySQL production database inserts the same record twice, but the primary key ID is different.
 
  Parse the delay-insert option of mysqldump
 
  Insert into xxx on duplicate key update in Mysql
 
  Mysql insert if not exists method to prevent repeated records from being inserted
 
  Usage of INSERT, UPDATE, DELETE, and REPLACE statements in MySQL Databases
 
  Mysql Operation summary INSERT and REPLACE

This article is an English version of an article which is originally in the Chinese language on aliyun.com and is provided for information purposes only. This website makes no representation or warranty of any kind, either expressed or implied, as to the accuracy, completeness ownership or reliability of the article or any translations thereof. If you have any concerns or complaints relating to the article, please send an email, providing a detailed description of the concern or complaint, to info-contact@alibabacloud.com. A staff member will contact you within 5 working days. Once verified, infringing content will be removed immediately.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

Sales Support

1 on 1 presale consultation

Chat Contact Sales
After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

Open a Ticket
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

Learn More