为防止主键冲突,设计DB的时候常常使用自增加(auto_increment 型)字段。因此插入数据前往往不知道改记录的主键是什么,为了方便后续或级联查询,我们需要在插入一行记录后获得DB自动生成的主键。这里稍微整理了下几种方法:
通用:
SELECT max(id) FROM user;
这个方法的缺点是不适合高并发。如果同时插入的时候返回的值可能不准确。
MySQL:
SELECT LAST_INSERT_ID();
重点: 假如你使用一条INSERT语句插入多个行, LAST_INSERT_ID() 只返回插入的第一行数据时产生的值。其原因是这使依靠其它服务器复制同样的 INSERT语句变得简单。
MS-SQL SERVER:
select @@IDENTITY;
@@identity是表示的是最近一次向具有identity属性(即自增列)的表插入数据时对应的自增列的值,是系统定义的全局变量。一般系统定义的全局变量都是以@@开头,用户自定义变量以@开头。比如有个表A,它的自增列是id,当向A表插入一行数据后,如果插入数据后自增列的值自动增加至101,则通过select @@identity得到的值就是101。使用@@identity的前提是在进行insert操作后,执行select @@identity的时候连接没有关闭,否则得到的将是NULL值。
补充:
SCOPE_IDENTITY、IDENT_CURRENT 和 @@IDENTITY 在功能上相似,因为它们都返回插入到 IDENTITY 列中的值。
IDENT_CURRENT 不受作用域和会话的限制,而受限于指定的表。IDENT_CURRENT 返回为任何会话和作用域中的特定表所生成的值。有关更多信息,请参见 IDENT_CURRENT。
SCOPE_IDENTITY 和 @@IDENTITY 返回在当前会话中的任何表内所生成的最后一个标识值。但是,SCOPE_IDENTITY 只返回插入到当前作用域中的值;@@IDENTITY 不受限于特定的作用域。
PHP: mysql_insert_id(connection); or mysqli_insert_id(connection);
参数 connection
描述 必需。规定要使用的 MySQL 连接。
<?php$con = mysql_connect("localhost", "hello", "321");if (!$con) { die('Could not connect: ' . mysql_error()); }$db_selected = mysql_select_db("test_db",$con);$sql = "INSERT INTO person VALUES ('Carter','Thomas','Beijing')";$result = mysql_query($sql,$con);echo "ID of last inserted record is: " . mysql_insert_id();mysql_close($con);?>
<?php$con=mysqli_connect("localhost","my_user","my_password","my_db");// Check connectionif (mysqli_connect_errno($con)){echo "Failed to connect to MySQL: " . mysqli_connect_error();}mysqli_query($con,"INSERT INTO Persons (FirstName,LastName,Age) VALUES ('Glenn','Quagmire',33)");// Print auto-generated idecho "New record has id: " . mysqli_insert_id($con); mysqli_close($con);?>
补充:
PHP-MySQL 是 PHP 操作 MySQL 资料库最原始的 Extension ,PHP-MySQLi 的 i 代表 Improvement ,提更了相对进阶的功能,就 Extension 而言,本身也增加了安全性。
a. mysql与mysqli的概念相关:
b. mysql与mysqli的区别:
c. mysql与mysqli的用法:
$conn = mysql_connect('localhost', 'user', 'password'); //连接mysql数据库mysql_select_db('data_base'); //选择数据库$result = mysql_query('select * from data_base');//第二个可选参数,指定打开的连接$row = mysql_fetch_row( $result ) ) //只取一行数据echo $row[0]; //输出第一个字段的值
PS:mysqli以过程式的方式操作,有些函数必须指定资源,比如mysqli_query(资源标识,SQL语句),并且资源标识的参数是放在前面的,而mysql_query(SQL语句,'资源标识')的资源标识是可选的,默认值是上一个打开的连接或资源。
$conn = new mysqli('localhost', 'user', 'password','data_base'); //要使用new操作符,最后一个参数是直接指定数据库//假如构造时候不指定,那下一句需要$conn -> select_db('data_base')实现$result = $conn -> query( 'select * from data_base' );$row = $result -> fetch_row(); //取一行数据echo row[0]; //输出第一个字段的值
使用new mysqli('localhost', usenamer', 'password', 'databasename');会报错,提示如下:
Fatal error: Class 'mysqli' not found in ...
一般是mysqli是没有开启的,因为mysqli类不是默认开启的,win下要改php.ini,去掉php_mysqli.dll前的;,linux下要把mysqli编译进去。
d. mysql_connect()与mysqli_connect()
JDBC 2.0:insertRow()
Statement stmt = null;ResultSet rs = null;try { stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, // 创建Statement java.sql.ResultSet.CONCUR_UPDATABLE); stmt.executeUpdate("DROP TABLE IF EXISTS autoIncTutorial"); stmt.executeUpdate( // 创建demo表 "CREATE TABLE autoIncTutorial (" + "priKey INT NOT NULL AUTO_INCREMENT, " + "dataField VARCHAR(64), PRIMARY KEY (priKey))"); rs = stmt.executeQuery("SELECT priKey, dataField " // 检索数据 + "FROM autoIncTutorial"); rs.moveToInsertRow(); // 移动游标到待插入行(未创建的伪记录) rs.updateString("dataField", "AUTO INCREMENT here?"); // 修改内容 rs.insertRow(); // 插入记录 rs.last(); // 移动游标到最后一行 int autoIncKeyFromRS = rs.getInt("priKey"); // 获取刚插入记录的主键preKey rs.close(); rs = null; System.out.println("Key returned for inserted row: " + autoIncKeyFromRS);} finally { // rs,stmt的close()清理}
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); // ... // 省略若干行(如上例般创建demo表) // ... stmt.executeUpdate( "INSERT INTO autoIncTutorial (dataField) " + "values ('Can I Get the Auto Increment Field?')", Statement.RETURN_GENERATED_KEYS); // 向驱动指明需要自动获取generatedKeys! int autoIncKeyFromApi = -1; rs = stmt.getGeneratedKeys(); // 获取自增主键! 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 { ... }

PHPsessionstrackuserdataacrossmultiplepagerequestsusingauniqueIDstoredinacookie.Here'showtomanagethemeffectively:1)Startasessionwithsession_start()andstoredatain$_SESSION.2)RegeneratethesessionIDafterloginwithsession_regenerate_id(true)topreventsessi

In PHP, iterating through session data can be achieved through the following steps: 1. Start the session using session_start(). 2. Iterate through foreach loop through all key-value pairs in the $_SESSION array. 3. When processing complex data structures, use is_array() or is_object() functions and use print_r() to output detailed information. 4. When optimizing traversal, paging can be used to avoid processing large amounts of data at one time. This will help you manage and use PHP session data more efficiently in your actual project.

The session realizes user authentication through the server-side state management mechanism. 1) Session creation and generation of unique IDs, 2) IDs are passed through cookies, 3) Server stores and accesses session data through IDs, 4) User authentication and status management are realized, improving application security and user experience.

Tostoreauser'snameinaPHPsession,startthesessionwithsession_start(),thenassignthenameto$_SESSION['username'].1)Usesession_start()toinitializethesession.2)Assigntheuser'snameto$_SESSION['username'].Thisallowsyoutoaccessthenameacrossmultiplepages,enhanc

Reasons for PHPSession failure include configuration errors, cookie issues, and session expiration. 1. Configuration error: Check and set the correct session.save_path. 2.Cookie problem: Make sure the cookie is set correctly. 3.Session expires: Adjust session.gc_maxlifetime value to extend session time.

Methods to debug session problems in PHP include: 1. Check whether the session is started correctly; 2. Verify the delivery of the session ID; 3. Check the storage and reading of session data; 4. Check the server configuration. By outputting session ID and data, viewing session file content, etc., you can effectively diagnose and solve session-related problems.

Multiple calls to session_start() will result in warning messages and possible data overwrites. 1) PHP will issue a warning, prompting that the session has been started. 2) It may cause unexpected overwriting of session data. 3) Use session_status() to check the session status to avoid repeated calls.

Configuring the session lifecycle in PHP can be achieved by setting session.gc_maxlifetime and session.cookie_lifetime. 1) session.gc_maxlifetime controls the survival time of server-side session data, 2) session.cookie_lifetime controls the life cycle of client cookies. When set to 0, the cookie expires when the browser is closed.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

WebStorm Mac version
Useful JavaScript development tools

mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

EditPlus Chinese cracked version
Small size, syntax highlighting, does not support code prompt function

DVWA
Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

SublimeText3 English version
Recommended: Win version, supports code prompts!
