last_insert_id()で返り値が０になってしまう

Question

前提・実現したいこと

phpmyadminで直接SQLに書き込んでいます。

insertしたデータのAUTOINCREMENTに設定したidを取得したいです。

last_insert_id()を使用して取得できると思うのですが、#1064のエラーが出てしまいます。

該当のソースコード

insert into cart_table (id, user_id, item_id, amount, created_date, updated_date) values (null, 0, 13, 1, now(), now()) select last_insert_id()

試したこと

https://teratail.com/questions/19089
参考にしてみて、select last_insert_id()のみでデータベースに入力したところ０しか返ってきませんでした。

###質問したいこと
そもそも使い方が間違っているのか？
insertした直後にselect last_insert_idを使用しないとidは取得できないのか？
０しか返って来ない理由をお聞きしたいです。

Comments

[m.ts10806]

PHP上で実行してますか？それともMySQL上で実行してますか？
PHPなのであればコードを提示してください。

[m.ts10806]

> insertした直後にselect last_insert_idを使用しないとidは取得できないのか？

上記だけに答えることはできますが、last_insert_id()を使用しなければならない要件なのでしたら、追記を待ちます。

[m.ts10806]

対象テーブルのCREATE文も提示願います。

[takashi5]

m.ts10806様、追記依頼ありがとうございます。情報が不足していると思い、alter tableでprimary keyの削除と追加を実施したことを書いたつもりでしたが、前提の欄で記入しなければなりませんでした。

>上記だけに答えることはできますが、last_insert_id()を使用しなければならない要件なのでしたら、追記を待ちます。
last_insert_id()でなければならない要件ではありません。より良い選択肢があるのであればご教示いただけますと幸いです。

[m.ts10806]

phpMyAdminの最後のキャプチャで出ているエラーは単にSQL文の区切り（;）がないからだと思います。
ひとまず、PHPは直接関係はないのでは？

[m.ts10806]

結局どのように解決されたのでしょう。
https://teratail.com/help/question-tips#questionTips4-2
回答にコメントでフィードバッグしてください。

Answer 1

試してみました。

環境：
Windows
XAMPP7.3.2

CREATE

CREATE TABLE `name` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `name`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

INSERT →　SELECT

insert into `name` (`name`) values ('hoge');
select last_insert_id();

2回目

INSERT →　SELECT

insert into `name` (`name`) values ('hoge2');
select last_insert_id();

できてますね。
提示された画像ではそもそもSQLのエラーが出ているので、そこはなおしましょう。
あと、あまり影響はないかと思いますが、AUTO_INCREMENTはカラム指定しなくても勝手にインクリメントしてくれるので、指定なしで試してみてください。

また、「AUTO_INCREMENTを使用しない方法」としては、「アプリケーション側でIDを払い出す」という方法です。
今回PHPをご利用でしたらPHP側で別途IDを払い出す仕組みを作ることですね。重複しなければいいので、これはどのような仕組みでも良いです（IDでソートするようなことがあれば連番になるように工夫が必要）

PDO::lastinsertidなど説明にあるように「異なる PDO ドライバ間で意味のあるもしくは 一貫性のある結果を返さないかも知れません。 」とある通り、期待した情報が得られない可能性があるからです。
「親情報をINSERTしてその親情報のIDをもって同時に子情報を作る」場合には私はアプリケーション側で払い出すようにしてます。

Comments

[takashi5]

m.ts1080様、ご回答ありがとうございます。再現できていなかったのはエラーを直せていなかったのが原因でした。指定なしでやってみました。親情報をINSERTしてその親情報のIDをもって同時に子情報を作る場合に異なるIDの情報の受け渡しがある可能性があるという解釈で間違い無いでしょうか？

質問にお答えして頂いただけでなく、丁寧な解説までして頂いてありがとうございます。

Answer 2

質問に書かれたURLの回答にAUTO_INCREMENT の使用

CREATE TABLE animals (

 id MEDIUMINT NOT NULL AUTO_INCREMENT,
 name CHAR(30) NOT NULL,
 PRIMARY KEY (id)

);

とあるのに、この質問に提示されたSQLにはPRIMARY KEY のDROP, ADD はあるけど(意味があるのか？)、CREATE TABLE が提示されていないので、AUTO_INCREMENT が設定されているか判断できません。エラーメッセージでAUTO_INCREMENT が設定されてないのだろう、って判断します。

Comments

[takashi5]

Orlofsky様、ご回答ありがとうございます。　質問の内容はMYSQLに直接書いたときの
質問となっております。質問に不備があり、お手数おかけてしまい申し訳ありません。

もしよろしければなのですが、Orlofsky様がこの状況であれば、どこに不備があってlast_insert_id()
が処理されないのかをお考えになるか教示していただけないでしょうか？

よろしくお願いします。

[Orlofsky]

SQLで使われるテーブルはCREATE TABLEされている必要がある、って理解できていますか？CREATE TABLEの中にAUTO_INCREMENTが設定されていないのでは？と書きました。

あと、ID(AUTO_INCREMENTが設定されている列)を指定しないで挿入する、も参考に。
https://qiita.com/sakuraya/items/0dd0bb4114e56f42556d

[takashi5]

Orlofsky様、回答ありがとうございます。MYSQLでデータベースを作る時にCREATE TABLE以外でテーブルを作成する方法を存じあげなかったので、軽視してしまいました。申し訳ございません。

qiita拝見させていただきました。ほぼ知らなかったことばかりだったので、とてもためになりました。
ありがとうございました。

Answer 3

insert into cart_table (id, user_id, item_id, amount, created_date, updated_date) values (null, 0, 13, 1, now(), now()); select last_insert_id();

セミコロンを入れたところidを取得できました。
最後にinsertしたidをPHP上で変数に代入ができなかったので、mysqli_insert_id()を使用して実現できました。

$sql = "insert into cart_table (id, user_id, item_id, amount, created_date, updated_date) values (null, 0, 13, 1, now(), now());"

$cart_id = mysqli_insert_id($link);//id取得