MySQL 如何在mysql中获取下一个自增id
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6761403/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get the next auto-increment id in mysql
提问by faressoft
How to get the next id in mysql to insert it in the table
如何在mysql中获取下一个id以将其插入表中
INSERT INTO payments (date, item, method, payment_code)
VALUES (NOW(), '1 Month', 'paypal', CONCAT("sahf4d2fdd45", id))
采纳答案by Sarfraz
Use LAST_INSERT_ID()from your SQL query.
使用LAST_INSERT_ID()从您的SQL查询。
Or
或者
You can also use mysql_insert_id()to get it using PHP.
您也可以mysql_insert_id()使用 PHP 来获取它。
回答by ravi404
You can use
您可以使用
SELECT AUTO_INCREMENT
FROM information_schema.tables
WHERE table_name = 'table_name'
AND table_schema = DATABASE( ) ;
or if you do not wish to use information_schema you can use this
或者如果你不想使用 information_schema 你可以使用这个
SHOW TABLE STATUS LIKE 'table_name'
回答by Johan
You can get the nextauto-increment value by doing:
您可以通过执行以下操作获取下一个自动增量值：
SHOW TABLE STATUS FROM tablename LIKE Auto_increment
/*or*/
SELECT `auto_increment` FROM INFORMATION_SCHEMA.TABLES
WHERE table_name = 'tablename'
Note that you should notuse this to alter the table, use an auto_increment column to do that automatically instead.
The problem is that last_insert_id()is retrospective and can thus be guaranteed within the current connection.
This baby is prospective and is therefore not unique per connection and cannot be relied upon.
Only in a single connection database would it work, but single connection databases today have a habit of becoming multiple connection databases tomorrow.
请注意，您应不使用此修改表，使用AUTO_INCREMENT列要做到这一点，而不是自动。
问题是这last_insert_id()是追溯性的，因此可以在当前连接内得到保证。
这个婴儿是潜在的，因此不是每个连接都是唯一的，不能依赖。
只有在单连接数据库中才能工作，但是今天的单连接数据库有明天变成多连接数据库的习惯。
See: SHOW TABLE STATUS
回答by Chaminda Bandara
This will return auto increment value for the MySQL database and I didn't check with other databases. Please note that if you are using any other database, the query syntax may be different.
这将返回 MySQL 数据库的自动增量值，我没有检查其他数据库。请注意，如果您使用任何其他数据库，查询语法可能会有所不同。
SELECT AUTO_INCREMENT
FROM information_schema.tables
WHERE table_name = 'your_table_name'
and table_schema = 'your_database_name';
SELECT AUTO_INCREMENT
FROM information_schema.tables
WHERE table_name = 'your_table_name'
and table_schema = database();
回答by Hexchaimen
The top answer uses PHP MySQL_for a solution, thought I would share an updated PHP MySQLi_solution for achieving this. There is no error output in this exmaple!
最佳答案使用 PHP MySQL_作为解决方案，我想我会分享一个更新的 PHP MySQLi_解决方案来实现这一点。这个例子中没有错误输出！
$db = new mysqli('localhost', 'user', 'pass', 'database');
$sql = "SHOW TABLE STATUS LIKE 'table'";
$result=$db->query($sql);
$row = $result->fetch_assoc();
echo $row['Auto_increment'];
Kicks out the next Auto increment coming up in a table.
踢出表中出现的下一个自动增量。
回答by Naresh Jain
In PHP you can try this:
在 PHP 中你可以试试这个：
$query = mysql_query("SELECT MAX(id) FROM `your_table_name`");
$results = mysql_fetch_array($query);
$cur_auto_id = $results['MAX(id)'] + 1;
OR
或者
$result = mysql_query("SHOW TABLE STATUS WHERE `Name` = 'your_table_name'");
$data = mysql_fetch_assoc($result);
$next_increment = $data['Auto_increment'];
回答by Angel
Solution:
解决方案：
CREATE TRIGGER `IdTrigger` BEFORE INSERT ON `payments`
FOR EACH ROW
BEGIN
SELECT AUTO_INCREMENT Into @xId
FROM information_schema.tables
WHERE
Table_SCHEMA ="DataBaseName" AND
table_name = "payments";
SET NEW.`payment_code` = CONCAT("sahf4d2fdd45",@xId);
END;
"DataBaseName" is the name of our Data Base
“DataBaseName”是我们数据库的名称
回答by Raa
Simple query would do
SHOW TABLE STATUS LIKE 'table_name'
简单的查询就可以了
SHOW TABLE STATUS LIKE 'table_name'
回答by Nick Pavluk
You can use mysql trigger
您可以使用mysql 触发器
回答by Jacob
You can't use the ID while inserting, neither do you need it. MySQL does not even know the ID when you are inserting that record. You could just save "sahf4d2fdd45"in the payment_codetable and use idand payment_codelater on.
插入时不能使用 ID，也不需要它。当您插入该记录时，MySQL 甚至不知道 ID。您可以将其保存"sahf4d2fdd45"在payment_code表格中id并payment_code稍后使用。
If you really need your payment_code to have the ID in it then UPDATE the row after the insert to add the ID.
如果您确实需要您的 payment_code 包含 ID，则在插入后更新行以添加 ID。
