douchi1945
2010-03-19 21:59
浏览 101
已采纳

mysql获取最后一个自动增量值

Hi I have a field in mySql table called jobnumber which auto increments with each new entry. What I want to do is when a user views my form, get the value of the next 'jobnumber' . But I want to do this before any entry is made to the table. IE so when a user looks at a form it will display something like 'this is job number 6954' I have tried $rr = mysql_insert_id() but this only work after I have made an entry

图片转代码服务由CSDN问答提供 功能建议

我在mySql表中有一个名为jobnumber的字段,它随每个新条目自动递增。 我想要做的是当用户查看我的表单时,获取下一个'jobnumber'的值。 但我想在对表格进行任何输入之前这样做。 IE所以当用户查看表单时,它会显示类似“这是作业号6954”的内容 我已经尝试过 $ rr = mysql_insert_id()但这只有在我输入后才能工作

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

4条回答 默认 最新

  • dsjws44266 2010-03-19 22:07
    已采纳

    (1) You could do it with looking at the last id in the table and incrementing it manually (but you won't be sure that the max(id)+1 is really the next id, because the last dataset might be deleted, ...):

    mysql>  select max(id)+1 from mytable;
    

    (2) Since MySQL does not support sequences like for example Oracle does, you can also implement something like a sequence manually. MySQL proposes the following:

    mysql>  CREATE TABLE sequence (id INT NOT NULL);
    mysql>  INSERT INTO sequence VALUES (0);
    
    mysql>  UPDATE sequence SET id=LAST_INSERT_ID(id+1);
    mysql>  SELECT LAST_INSERT_ID();
    

    Also take a look at this link for more information.

    点赞 评论
  • duanqin9631 2010-03-19 22:04

    You could try a query along the lines of "SELECT MAX(jobNumber) FROM JobTable;" and display that number (plus one). As long as the id has always been sequentially auto-numbered and no one else performs an insert between that query and the submission of the form it should be correct.

    点赞 评论
  • doulu4534 2012-12-25 01:45

    There are at least a few problems regarding your approach. First of all, there is virtually no possibility to know that number reliably. In order to "reserve" that number, you need one of 2 options:

    1: lock the table for all clients until this one decides to insert something, therefore being able to guess the next auto increment (as you can guess, pretty bad)

    2: Trying to "reserve" the auto incremented field by forcing a temporary insert (while this looks better, it is not, because it will lead to alot of unusuable primary keys, just sitting there).

    Choose either one of them you like, or just drop completely this tiny bit of functionality.

    点赞 评论
  • duaner5714 2013-07-24 09:22
    $ret = mysqli_query("SHOW TABLE STATUS LIKE 'table' ");
    $row = mysqli_fetch_array($ret);
    $Auto_increment = $row['Auto_increment'];
    mysqli_free_result($ret);
    
    点赞 评论

相关推荐 更多相似问题