Third, use the following SELECT statement to verify the inserts: SELECT * FROM projects Note that when you insert multiple rows and use the LAST_INSERT_ID() function to get the last inserted id of an AUTO_INCREMENT column, you will get the id of the first inserted row only, not the id of the last inserted row. It means that two rows have been inserted into the projects table successfully. MySQL issued the following message: 2 row(s) affected Second, use the INSERT multiple rows statement to insert two rows into the projects table: INSERT INTO Let’s take an example of using the INSERT multiple rows statement.įirst, create a new table called projects for the demonstration: CREATE TABLE projects( SELECT statement can insert as many rows as you want. Note that the max_allowed_packet has no influence on the INSERT INTO. Where size is an integer that represents the number the maximum allowed packet size in bytes. To set a new value for the max_allowed_packet variable, you use the following statement: SET GLOBAL max_allowed_packet= size The number is the Value column is the number of bytes. Note that the value in your server may be different. Here is the output on our MySQL database server. This statement shows the current value of the max_allowed_packet variable: SHOW VARIABLES LIKE 'max_allowed_packet' However, when MySQL server receives the INSERT statement whose size is bigger than max_allowed_packet, it will issue a packet too large error and terminates the connection. In theory, you can insert any number of rows using a single INSERT statement. The number of values in each element must be the same as the number of columns in the column_list. Each element of the list represents a row.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |