[PHP] obtain the primary key of the inserted data from MySQL (auto-increment ID)

[PHP] MySQL obtains the primary key (auto-increment ID) of the inserted data. to prevent primary key conflicts, the auto-increment (auto_increment type) field is often used during database design. Therefore, the primary key of the modified record is often unknown before data is inserted. to facilitate subsequent or cascade queries, we need to obtain the primary key automatically generated by the database after inserting a row of record. Here we have sorted out the following methods:

DB query

General:

SELECT max(id) FROM user;

The disadvantage of this method is that it is not suitable for high concurrency. The returned values may be inaccurate at the same time.

MySQL:

SELECT LAST_INSERT_ID();

Note: If you use an INSERT statement to INSERT multiple rows, LAST_INSERT_ID () only returns the value generated when inserting the first row of data. This makes it easy to copy the same INSERT statement on other servers.

MS-SQL SERVER:

select @@IDENTITY;

@ Identity refers to the value of the auto-incrementing column corresponding to the last time data is inserted into a table with the identity attribute (that is, the auto-incrementing column). It is a global variable defined by the system. Generally, global variables defined by the system start with @ and user-defined variables start. For example, if the auto-increment column of Table A is id, after A row of data is inserted into table A, if the value of the auto-increment column is automatically increased to 101 after the data is inserted, the value obtained through select @ identity is 101. The premise of using @ identity is that the connection is not closed when select @ identity is executed after the insert operation. Otherwise, the value is NULL.

Supplement:
SCOPE_IDENTITY, IDENT_CURRENT, and @ IDENTITY are functionally similar, because they all return values inserted into the IDENTITY column.

IDENT_CURRENT is not restricted by the scope and session, but by the specified table. IDENT_CURRENT returns the value generated for a specific table in any session and scope. For more information, see IDENT_CURRENT.

SCOPE_IDENTITY and @ IDENTITY return the last id value generated in any table in the current session. However, SCOPE_IDENTITY only returns the value inserted into the current scope; @ IDENTITY is not limited to a specific scope.

Server language query

PHP: mysql_insert_id (connection );OrMysqli_insert_id (connection );

Parameter connection

Description required. Specifies the MySQL connection to be used.

Supplement:

PHP-MySQL is the most primitive Extension for PHP to operate MySQL databases. I of PHP-MySQLi stands for Improvement and provides more advanced functions. Extension also increases security.

A. The concepts of mysql and mysqli are related:

Mysql and mysqli are both function sets in php and are not associated with mysql databases.

Before php5, php mysql functions are generally used to drive mysql databases. for example, mysql_query () functions are process-oriented. 3. after php5, the function of mysqli is added. in a sense, it is an enhanced version of mysql system functions, which is more stable, efficient, and secure. mysqli_query () corresponds to mysql_query (), it is an object-oriented, and uses an object-driven mysql database.

B. differences between mysql and mysqli:

Mysql does not support the connection function. each connection to mysql opens a connection process.

Mysqli is a permanent connection function. mysqli runs mysqli multiple times and uses the same connection process to reduce the overhead of the server. Mysqli encapsulates some advanced operations, such as transactions, and many available methods in the database operation process.

C. usage of mysql and mysqli:

Mysql (procedure ):

$ Conn = mysql_connect ('localhost', 'user', 'password'); // Connect to mysql database mysql_select_db ('data _ base '); // select database $ result = mysql_query ('select * from data_base '); // The Second Optional parameter, specifying the connection to be opened $ row = mysql_fetch_row ($ result )) // Obtain only one row of data echo $ row [0]; // output the value of the first field

PS: mysqli operates in a procedural manner. some functions must specify resources, such as mysqli_query (resource ID, SQL statement), andThe parameters of the resource ID are placed beforeThe resource ID of mysql_query (SQL statement, 'resource identified') is optional. the default value is the previous opened connection or resource.

Mysqli (object method ):

$ Conn = new mysqli ('localhost', 'user', 'password', 'data _ base'); // use the new operator, the last parameter is to directly specify the database // If this parameter is not specified during the construction, the next sentence requires $ conn-> select_db ('data _ base ') implement $ result = $ conn-> query ('select * from data_base '); $ row = $ result-> fetch_row (); // Retrieve a row of data echo row [0]; // output the value of the first field

When new mysqli ('localhost', usenamer ', 'password', 'databasename') is used, an error is returned, and the prompt is as follows:

Fatal error: Class 'mysqli' not found in...

In general, mysqli is not enabled, because the mysqli class is not enabled by default. in Windows, you need to change php. ini and remove php_mysqli.dll; in linux, you need to compile mysqli.

D. mysql_connect () and mysqli_connect ()

Using mysqli, you can pass the database name as a parameter to the mysqli_connect () function, or to the mysqli constructor;

If you call the mysqli_query () or mysqli object query () method, the connection ID is required.

JDBC 2.0: insertRow ()

Statement stmt = null; ResultSet rs = null; try {stmt = conn. createStatement (java. SQL. resultSet. TYPE_FORWARD_ONLY, // Create Statement java. SQL. resultSet. CONCUR_UPDATABLE); stmt.exe cuteUpdate ("drop table if exists autoIncTutorial"); stmt.exe cuteUpdate (// CREATE demo TABLE "create table autoIncTutorial (" + "priKey int not null AUTO_INCREMENT, "+" dataField VARCHAR (64), primary key (priKey) "); rs = stmt.exe cuteQ Uery ("SELECT priKey, dataField" // Retrieve data + "FROM autoIncTutorial"); rs. moveToInsertRow (); // Move the cursor to the row to be inserted (uncreated pseudo record) rs. updateString ("dataField", "auto increment here? "); // Modify the rs content. insertRow (); // Insert record rs. last (); // Move the cursor to the last line int autoIncKeyFromRS = rs. getInt ("priKey"); // Obtain the primary key preKey rs of the newly inserted record. close (); rs = null; System. out. println ("Key returned for inserted row:" + autoIncKeyFromRS);} finally {// rs, stmt close () Cleanup}

JDBC 3.0: getGeneratedKeys ()

Statement stmt = null; ResultSet rs = null; try {stmt = conn. createStatement (java. SQL. resultSet. TYPE_FORWARD_ONLY, java. SQL. resultSet. CONCUR_UPDATABLE );//... // omit several rows (create demo table as shown in the preceding example )//... stmt.exe cuteUpdate ("insert into autoIncTutorial (dataField)" + "values ('Can I Get the Auto Increment Field? ') ", Statement. RETURN_GENERATED_KEYS); // specifies to the driver that the generatedKeys must be automatically obtained! Int autoIncKeyFromApi =-1; rs = stmt. getGeneratedKeys (); // get the auto-incrementing primary key! If (rs. next () {autoIncKeyFromApi = rs. getInt (1);} else {// throw an exception from here} rs. close (); rs = null; System. out. println ("Key returned from getGeneratedKeys ():" + autoIncKeyFromApi);} finally {...}