I would like to retrieve the id of a row I just inserted.<\/p>\n
In Oracle this problem is handled with the use of sequences,<\/p>\n
\r\nSQL> SELECT uid_seq.NEXTVAL INTO user_id FROM DUAL;\r\nSQL> INSERT INTO users (id, username) VALUES (user_id, 'joebob');\r\n1 row created.\r\n\r\nSQL> -- do something else with user_id\r\n<\/pre>\nBasically, a sequence can provide you, a-priori, a unique number safe to use in an insert statement. Concurrent calls to a sequence.NEXTVAL will be unique.<\/p>\n
In mysql there are no sequences. Instead, an AUTO-INCREMENT field is often used and the LAST_INSERT_ID() will return the value in question. For example,<\/p>\n
\r\nmysql> INSERT INTO users (username) VALUES ('joebob');\r\nQuery OK, 1 row affected (0.01 sec)\r\n\r\nmysql> SELECT LAST_INSERT_ID();\r\n+------------------+\r\n| LAST_INSERT_ID() |\r\n+------------------+\r\n| 7 |\r\n+------------------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql>\r\n<\/pre>\nThese two solutions often spill over and arguably break encapsulation of the data-access layer, leaving your code dependent on a specific database driver (and database engine). For example, in PHP (using a PDO interface) a sequence name is often required in order to use the correct sequence:<\/p>\n
\r\n<?php\r\n\r\n \/\/ if there was an auto-increment\r\n $conn->exec('INSERT INTO table (data) VALUES(255)');\r\n $id = $conn->lastInsertId();\r\n\r\n \/\/ but if there was a sequence\r\n $conn->exec('INSERT INTO table (id, data) VALUES(sequence_name.NEXTVAL, 255)');\r\n $id = $conn->lastInsertId('sequence_name');\r\n\r\n?>\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"I would like to retrieve the id of a row I just inserted. In Oracle this problem is handled with the use of sequences, SQL> SELECT uid_seq.NEXTVAL INTO user_id FROM DUAL; SQL> INSERT INTO users (id, username) VALUES (user_id, ‘joebob’); 1 row created. SQL> — do something else with user_id Basically, a sequence can provide […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[16,15,7],"tags":[],"_links":{"self":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/82"}],"collection":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/comments?post=82"}],"version-history":[{"count":9,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/82\/revisions"}],"predecessor-version":[{"id":736,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/82\/revisions\/736"}],"wp:attachment":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/media?parent=82"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/categories?post=82"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/tags?post=82"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}