php - Get next Auto Increment from MySQL table with PDO (without INSERT)
注意, 使用以下任意方法都会将现有数据删除. 方法一: delete from tb1;ALTER TABLE tbl AUTO_INCREMENT = 100; （好处, 可以设置 AUTO_INCREMENT 为任意值开始
I have an mysql table with an column called "id" - this column has auto increment set.
I only want to know how i can get, with PHP and PDO, the next auto increment value, without doing a INSERT INTO query. (i found some questions about this - but none without INSERT INTO and only based on mysqli).
Thanks!php mysql pdo auto-increment
this question edited Aug 29 '13 at 10:35 RiggsFolly 52.1k 15 50 77 asked Aug 29 '13 at 10:31 Mike 73 1 3 16 Why do you need it? You can't use it if you simply read it in advance, because you'll run into concurrency issues then – Mark Baker Aug 29 '13 at 10:34 @MarkBaker There are a number of reasons you would want to do this, perhaps using the id as the seed to a display id. The concurrency issue is easily solved by using a transaction. – Louis Jan 16 '15 at 4:24 A transaction won't be retained across multiple http requests, ie a GET to display and a POST to insert/update – Mark Baker Jan 16 '15 at 7:57
Why do you need this ?
If you need, you can get the current auto increment value like this, but I don't see a situation where this would be needed.
SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'DatabaseName' AND TABLE_NAME = 'TableName';
this answer answered Aug 29 '13 at 10:35 Vlad Preda 5,598 2 24 54 This is good to know. – allen213 Aug 29 '13 at 10:35 1 Just because you can't see a need doesn't mean there isn't one. /sigh – Louis Jan 16 '15 at 4:26
The very statement of the question is wrong.
You actually don't need it, as without the INSERT query this value makes absolutely no sense.
Although I do understand that simplicity of PHP language welcomes anyone into the world of web-programming, it still requires some knowledge. One of the things you have to understand is the purpose and the meaning of the autoincremented unique identifier. Which is not what you think. It makes sense ONLY after insert and nowhere else.
So, there could be only two possible scenarios:
- either you really need a freshly generated id - then just insert a record, get that id and use it
- or you are confusing this value with something else and thus don't need it at all
this answer edited Aug 29 '13 at 11:12 allen213 1,199 1 9 19 answered Aug 29 '13 at 10:44 Your Common Sense 118k 20 114 196 1 This answer just says the same thing as the ones below "I don't see a situation where this would be needed." equates to what your are saying in your bullet points. I think your comment should have said using the suggested query wasn't advisable. – allen213 Aug 29 '13 at 11:03
I can see reasons why you would want / need this. We use it as part of security for new clients which keeps the 'keys' unique without having to send additional queries to the DB.
$q = $db->query("SHOW TABLE STATUS LIKE 'yourtable'"); $next = $q->fetch(PDO::FETCH_ASSOC); echo $next['Auto_increment'];
this answer answered Aug 15 '15 at 20:11 Sabyre 18 7
- 4【PHP PDO】纯PHP（不使用框架）下 Mysql PDO 使用方法小记
- 5php PDO连接mysql