"PHP" MySQL gets the primary key for inserting data (self-increment id), MySQL primary key _php tutorial

"PHP" MySQL gets the primary key for inserting data (self-increment id), MySQL primary key

To prevent primary key collisions, the self-increment (auto_increment) field is often used when designing db. So before inserting the data, we often don't know what the primary key of the record is, and in order to facilitate subsequent or cascading queries, we need to get the DB auto-generated primary key after inserting a row of records. Here are a few ways to sort out the following:

• Query in DB

General:
SELECT Max  from user;
The disadvantage of this method is that it is not suitable for high concurrency. If you insert it at the same time, the value returned may not be accurate.
Mysql:
SELECT LAST_INSERT_ID ();
IMPORTANT: If you use an INSERT statement to insert multiple rows, last_insert_id () returns only the value produced when the first row of data is inserted. The reason for this is that it makes it easier to rely on other servers to replicate the same INSERT statement.
Ms-sql SERVER:
Select @ @IDENTITY;
@ @identity is the value of the last self-increment that corresponds to the most recent insertion of data into a table with the identity attribute (that is, the self-increment column), which is system defines a global variable. General system-defined global variables start with @@ 开头 and user-defined variables begin with @. For example, there is a table A, its self-increment column is an ID, when inserting a row of data into a table, if the value of the self-increment after inserting data automatically increases to 101, then the value obtained by the SELECT @ @identity is 101. The @ @identity is used only if the connection is not closed when the insert operation is executed, or a null value is obtained. 

 
  
 
Add:
The scope_identity, ident_current, and @ @IDENTITY are functionally similar because they all return values that are inserted into the IDENTITY column.
Ident_current is not subject to scope and session restrictions, but is limited to the specified table. Ident_current returns the value generated for a specific table in any session and scope. For more information, see ident_current.
The scope_identity and @ @IDENTITY return the last identity value generated within any table in the current session. However, scope_identity only returns the value inserted into the current scope, and the @ @IDENTITY is not restricted to a specific scope.

• Server language Query

php:mysql_insert_id (connection); or mysqli_insert_id (connection); 
Parameter connection    
Description required. Specifies the MySQL connection to use.
 
   PHP$conmysql_connect("localhost", "Hello", "321"); if (! $con )  {  diemysql_error());  } $db _selected mysql_select_db ("test_db",$con); $sql = "INSERT into person VALUES (' Carter ', ' Thomas ', ' Beijing ')"; $result mysql_query ($sql,$con); Echo mysql_insert_id (); Mysql_close ($con);? >
 
   php$con=mysqli_connect("localhost", "My_user", "My_password", "my_db"); // Check Connection if (mysqli_connect_errno($con)) {echomysqli_connect_error( );} Mysqli_query ($con, "INSERT into Persons (firstname,lastname,age) VALUES (' Glenn ', ' Quagmire ',") "); // Print auto-generated ID Echo mysqli_insert_id ($conmysqli_close($con);? >
Add:
Php-mysql is the PHP operation MySQL Database The most primitive Extension, php-mysqli I for improvement, to mention the relative advanced function, in Extension, itself also increased security.
A. MySQL is related to the concept of mysqli:

 
 

  
  
 
   
MySQL and mysqli are both functional sets of PHP and are not associated with MySQL databases.
 
   
Before the PHP5 version, the general is to use the PHP MySQL function to drive the MySQL database, such as mysql_query () function, belongs to process 3, after the PHP5 version, added mysqli function function, in a sense, It is an enhanced version of MySQL system functions, more stable and more efficient and more secure, with mysql_query () corresponding to the Mysqli_query (), belongs to the object-oriented, the operation of the object to drive the MySQL database
 
  
 
 
B. The difference between MySQL and mysqli:

 
 

  
  
 
   
MySQL is a non-holding connection function, and the MySQL link will open a connected process each time.
 
   
Mysqli is a forever-connected function, mysqli multiple runs mysqli will use the same connection process, thus reducing the server overhead. Mysqli encapsulates some of the advanced operations, such as transactions, while encapsulating many of the methods available in the DB operation process.
 
  
 
 
C. mysql and mysqli usage:

 
 

  
  
 
   
MySQL (process mode):
 
  
 
 
$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 that specifies the open connection $row Mysql_fetch_row $result // fetch only one row of data Echo $row // output The value of the first field
Ps:mysqli operate in a procedural way, some functions must specify a resource, such as mysqli_query (resource ID, SQL statement), and the parameters of the resource identifier are placed in front , and the mysql_query (SQL statement, ' resource ID ') The resource ID is optional and the default value is the last open connection or resource.

 
 

  
  
 
   
Mysqli (object mode):
 
  
 
 
$conn　　new mysqli (' localhost ', ' user ', ' password ', ' data_base '); // to use the new operator, the last parameter is to specify the database directly//If the construction time is not specified, then the next sentence needs $conn-select_db (' data_base ') implementation $result $conn , query (' SELECT * from Data_base ' ); $row $result // fetch a row of data Echo // output The value of the first field
Use new mysqli (' localhost ', usenamer ', ' Password ', ' databasename ');
Fatal error:class ' mysqli ' not found in ...
Generally mysqli is not open, because Mysqli class is not the default open, win under to change php.ini, remove php_mysqli.dll ago, Linux to put Mysqli in.
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 constructor of Mysqli;
 
   
The connection ID is required if you call Mysqli_query () or Mysqli's object query () method.
 
  
 
 
 
  
 
JDBC 2.0:insertrow ()
Statement stmt =NULL; ResultSet RS=NULL;Try{stmt= Conn.createstatement (Java.sql.ResultSet.TYPE_FORWARD_ONLY,//Create statementJava.sql.ResultSet.CONCUR_UPDATABLE); Stmt.executeupdate ("DROP TABLE IF EXISTS autoinctutorial"); Stmt.executeupdate (//Create a demo table"CREATE TABLE autoinctutorial (" + "Prikey INT not NULL auto_increment," + "DataField VARCHAR (64), PRIMARY KEY (Prikey)) "); RS= Stmt.executequery ("Select Prikey, DataField"//Retrieving Data+ "from Autoinctutorial");                                              Rs.movetoinsertrow (); //move cursor to row to insert (pseudo record not created)Rs.updatestring ("DataField", "AUTO INCREMENT here?");//Modify ContentRs.insertrow ();//Inserting RecordsRs.last ();//move cursor to last row    intAutoinckeyfromrs = Rs.getint ("Prikey");//gets the primary key prekey of the record you just insertedRs.close (); RS=NULL; System.out.println ("Key returned for inserted row:" +Autoinckeyfromrs);} finally {    //Close () Cleanup of rs,stmt}
 
  
 
JDBC 3.0:getgeneratedkeys ()
Statement stmt =NULL; ResultSet RS=NULL;Try{stmt=conn.createstatement (Java.sql.ResultSet.TYPE_FORWARD_ONLY, Java.sql.ResultSet.CONCUR_U      pdatable); // ...    //omit several lines (create demo table as above example)// ...Stmt.executeupdate ("INSERT into Autoinctutorial (DataField)" + "values (' Can I Get the Auto Increment Field? ')", Statement.return_generated_keys); //indicates to the driver that an automatic acquisition of generatedkeys! is required     intAutoinckeyfromapi =-1; RS= Stmt.getgeneratedkeys ();//get the self-increment 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{ ... }

http://www.bkjia.com/PHPjc/971765.html www.bkjia.com true http://www.bkjia.com/PHPjc/971765.html techarticle "PHP" MySQL gets the primary key for inserting data (self-increment id), MySQL primary key to prevent primary key conflict, when designing DB often use the self-increment (auto_increment type) field. So insert data ...

