Mysql insert statement optimization, mysqlinsert statement

Mysql insert statement optimization, mysqlinsert statement
1) if you INSERT many rows from the same customer at the same time, use the INSERT Statement of multiple value tables. This is faster than using separate INSERT statements (several times in some cases ).
Insert into test values (1, 2), (1, 3), (1, 4 )...

2) If you INSERT many rows from different customers, you can use the insert delayed statement to get a higher speed. The meaning of Delayed is to let the insert statement be executed immediately. In fact, the data is put in the memory queue and is not actually written to the disk; this is much faster than inserting each statement separately; LOW_PRIORITY, on the contrary, is inserted only after all other users read the table.

3) store index files and data files on different disks (using the options in Table creation ).

4) if batch inserts are performed, you can increase the speed by adding the value of the bulk_insert_buffer_size variable. However, this can only be used for myisam tables.

5) when loading a table from a text file, load data infile is used. This is usually 20 times faster than using many INSERT statements.

6) replace insert With the replace statement as needed.

7) ignore duplicate records by using the ignore keyword based on application conditions.

INSERT

First query and then insert

How Mysql uses the insert statement

You can use either of the following methods:
Insert problemprocess values (null, "it's a useful book ");
Insert problemprocess values (null, 'its' a useful book ');
Insert problemprocess values (null, 'it \'s a useful Book ');

In the database application, you can replace the collected strings, for example:
Sometext = sometext. replaceAll ("\'","\'\'")
Sometext = sometext. replaceAll ("\"","\\\"")
Sometext = sometext. replaceAll ("\,","\\\\")
....

If you use jdbc to operate databases in java, you can also use PreparedStatement, for example:
PreparedStatement pstmt = connection. prepareStatement (SQL );
....
String sometext = textfield. getTest (); -- obtains the content entered by the user.
Pstmt. setInt (2, sometext );
Pstmt.exe cuteUpdate ();