MySQL insert statement usage and optimization tutorial
- 2020-12-09 01:04:42
- OfStack
The INSERT INTO SQL statement is used in the MySQL table to insert data.
You can go through mysql
>
Insert data into the table in the command prompt window or through the PHP script.
grammar
The following is the common INSERT INTO SQL syntax for inserting data into the MySQL data table:
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );
If the data is of character type, single or double quotation marks must be used, such as "value".
Insert data through a command prompt window
Below we will use the SQL INSERT INTO statement to insert data into the MySQL data table runoob_tbl
The instance
In the following example we will insert three pieces of data into the runoob_tbl table:
root@host# mysql -u root -p password;
Enter password:*******
mysql> use RUNOOB;
Database changed
mysql> 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: Use the arrow mark (-
>
) is not a part of the SQL statement, it only represents 1 new line. If an SQL statement is too long, we can write the SQL statement by creating 1 new line with the enter key. The SQL statement ends with a semicolon (;). .
In the above example, we did not provide data for runoob_id because this field was set to the AUTO_INCREMENT(auto add) attribute when we created the table. So, the field is automatically incremented without us having to set it. In the example, NOW() is an MySQL function that returns a date and time.
Use the PHP script to insert the data
You can use the mysql_query() function of PHP to execute the SQL INSERT INTO command to insert data.
This function takes two arguments and returns TRUE on success or FALSE on failure.
grammar
bool mysql_query( sql, connection );
Parameters:
sql: Required, specifies the SQL query to be sent. Note that the query string should not end with a semicolon.
connection: Optionally, specify the SQL connection identifier or, if not, use the last open connection.
The instance
In the following example, the program receives data from three fields entered by the user and inserts it into a data table:
<html>
<head>
<title> to MySQL Add data to database </title>
</head>
<body>
<?php
if(isset($_POST['add']))
{
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Could not connect: ' . mysql_error());
}
if(! get_magic_quotes_gpc() )
{
$runoob_title = addslashes ($_POST['runoob_title']);
$runoob_author = addslashes ($_POST['runoob_author']);
}
else
{
$runoob_title = $_POST['runoob_title'];
$runoob_author = $_POST['runoob_author'];
}
$submission_date = $_POST['submission_date'];
$sql = "INSERT INTO runoob_tbl ".
"(runoob_title,runoob_author, submission_date) ".
"VALUES ".
"('$runoob_title','$runoob_author','$submission_date')";
mysql_select_db('RUNOOB');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die('Could not enter data: ' . mysql_error());
}
echo "Entered data successfully\n";
mysql_close($conn);
}
else
{
?>
<form method="post" action="<?php $_PHP_SELF ?>">
<table width="600" border="0" cellspacing="1" cellpadding="2">
<tr>
<td width="250">Tutorial Title</td>
<td>
<input name="runoob_title" type="text" id="runoob_title">
</td>
</tr>
<tr>
<td width="250">Tutorial Author</td>
<td>
<input name="runoob_author" type="text" id="runoob_author">
</td>
</tr>
<tr>
<td width="250">Submission Date [ yyyy-mm-dd ]</td>
<td>
<input name="submission_date" type="text" id="submission_date">
</td>
</tr>
<tr>
<td width="250"> </td>
<td> </td>
</tr>
<tr>
<td width="250"> </td>
<td>
<input name="add" type="submit" id="add" value="Add Tutorial">
</td>
</tr>
</table>
</form>
<?php
}
?>
</body>
</html>
When we receive user-submitted data, we need to use the get_magic_quotes_gpc() function to determine whether the escape of special characters has been turned on for the security of the data. If this option is off (not on) and returns 0, then we have to call the addslashes function to escape the string.
Righteousness.
You can also add other methods to check data, such as email format validation, phone number validation, integer validation, etc.
Optimize insert performance
The insert statement syntax for mysql
insert into `table`(`field1`,`field2`) values('value1','value2');
Ways to improve insert performance
1.1 sql statements insert multiple pieces of data
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 as
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;
Pay attention to
1. The length of sql statement is limited, so pay attention to merging sql statement. The length limit can be modified with the max_allowed_packet configuration item, which defaults to 1M.
2. Transactions are too large to affect the efficiency of execution. mysql has innodb_log_buffer_size configuration item, exceeding which will use disk data and affect the efficiency of execution.
Configuration item description for transactions:
1.innodb_buffer_pool_size
If Innodb is used, this is an important variable. Innodb is more sensitive to buffer size than MyISAM. MySIAM may be fine using the default key_buffer_size for large data volumes, but Innodb feels like it's crawling around with the default. Innodb's buffer pool caches data and indexes, so there is no need to cache the system. If you only use Innodb, you can set this value to 70-80% of memory. As with key_buffer, you can also increase memory usage by not setting this value too high if the amount of data is small and does not increase.
2.innodb_additional_pool_size
The effect is not obvious, at least when the operating system allocates memory properly. But you might still need to set it to 20M or more 1 point to see how much memory Innodb will allocate for other purposes.
3.innodb_log_file_size
It's very important when you're writing a lot of stuff especially big data. Note that large files provide higher performance, but database recovery takes more time. I usually use 64M-512M, depending on the server space.
4.innodb_log_buffer_size
Default values are fine for most moderate write operations and for short transactions. If you update frequently or use a lot of blob data, you should increase this value. But too much is also a waste of memory, as one second is always flush. 1 time, so there is no need to set the requirement to more than 1 second. 8M-16M1 should be enough. Small applications can be set to be less than 1.
5.innodb_flush_log_at_trx_commit
Complaining that Innodb is 100 times slower than MyISAM? So you probably forgot to adjust this value. The default value of 1 means that the log must be written to the (flush) hard disk for every transaction commit or out-of-transaction instruction, which is time-consuming. This is especially true when using the power pool power cache (Battery backed up cache). Setting it to 2 is fine for many applications, especially for MyISAM tables, which means writing not to the hard disk but to the system cache. The logs will still be flush to the hard disk every second, so you won't lose more than 1-2 seconds of updates. Setting it to 0 is faster at 1 point, but the security aspect is poor, and even if MySQL hangs, you may lose transaction data. A value of 2 will only lose data if the entire operating system hangs.