LoginSignup
0
0
@count3

MySQLではinsertで生成されるIDが帰ってこない問題へのアプローチ

Last updated at Posted at 2022-09-28

注）mysql 5.6のことだけ調べました。他のバージョンのことはよくわからないです。

この記事でできるようになること

mysqlでinsertしたレコードの自動生成されたIDの取得

解決したかった問題

Railsでbulk insertする場合

  • 生SQLでinsert文流す
  • activerecord-importを使う

を使うのかなと思うのですが、mysqlは生成したレコードのIDがわからない、、、

生成したIDで紐付け処理を行う場合は、再度fetchしてIDを取得する必要がある。
が、取得する場合にkey値がなかったらどうすればいいのか

こんなデータ

mysql> desc parents;
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| id         | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255)        | NO   |     | NULL    |                |
| created_at | datetime            | NO   |     | NULL    |                |
| updated_at | datetime            | NO   |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+

mysql> desc children;
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| id         | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255)        | NO   |     | NULL    |                |
| parent_id  | bigint(20) unsigned | NO   | MUL | NULL    |                |←これがfkになっている。
| created_at | datetime            | NO   |     | NULL    |                |
| updated_at | datetime            | NO   |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+
(CONSTRAINT `fk_child_parent` FOREIGN KEY (`parent_id`) REFERENCES `parents` (`id`) がついているイメージ)

railsだと

class Parent < ApplicationRecord
  has_many :children
end

class Child < ApplicationRecord
  belongs_to :parent
end

こんな処理

def insert_many_times
    children = (1..10).flat_map do |i|
        (1..2).map do |j|
        Child.new(name: "name_#{i}_#{j}").tap { |c| c.parent = Parent.new(name: "name_#{i}") }
        end
    end
    children.each(&:save)
end

irb(main):01:0> insert_many_times

TRANSACTION (0.2ms)  BEGIN
Parent Create (6.3ms)  INSERT INTO `parents` (`name`, `created_at`, `updated_at`) VALUES ('name_1', '2021-08-27 02:45:05', '2021-08-27 02:45:05')
Child Create (4.3ms)  INSERT INTO `children` (`name`, `parent_id`, `created_at`, `updated_at`) VALUES ('name_1_1', 72, '2021-08-27 02:45:05', '2021-08-27 02:45:05')
TRANSACTION (6.9ms)  COMMIT

以下同じinsert分が10回流れる。。。。

mysqlはinsertのSQLでidって返ってきません。
postgresqlだったらとれるんですが、、、

mysql> insert into parents(name, created_at, updated_at) values('aaa',now(),now());
Query OK, 1 row affected (0.01 sec)

作成されたレコードのIDの取得

mysqlでinsertされたidを取得する方法を調べてみる。
https://dev.mysql.com/doc/refman/5.6/ja/mysql-insert-id.html

前の INSERT または UPDATE ステートメントによって、AUTO_INCREMENT カラムに生成された値を返します。この関数は、AUTO_INCREMENT フィールドを含むテーブルへのINSERT ステートメントを実行したか、INSERT または UPDATE を使用して、LAST_INSERT_ID(expr) によってカラム値を設定した後に使用します。
mysql_insert_id() の値は、現在のクライアント接続内で発行されたステートメントによってのみ影響を受けます。それは、ほかのクライアントによって発行されたステートメントに影響を受けません。

なるほど。こいつは使えそうだ。

mysql_insert_id() は、AUTO_INCREMENT 値を生成するストアドプロシージャーの CALL ステートメントのあとに、0 を返します。この場合、mysql_insert_id() が CALL に適用され、プロシージャー内のステートメントには適用されないためです。プロシージャー内で、LAST_INSERT_ID() を SQL レベルで使用して、AUTO_INCREMENT 値を取得できます。
LAST_INSERT_ID() と mysql_insert_id() の違いの理由は、LAST_INSERT_ID() がスクリプトで使いやすく、mysql_insert_id() は AUTO_INCREMENT カラムに起こったことに関してより正確な情報を提供しようとすることです。

試す

LAST_INSERT_IDを試す。

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                0 |
+------------------+
                                                        mysql> SELECT LAST_INSERT_ID();
                                                        +------------------+
                                                        | LAST_INSERT_ID() |
                                                        +------------------+
                                                        |                0 |
                                                        +------------------+
                                                        1 row in set (0.00 sec)
mysql> insert into parents(name, created_at, updated_at) values('aaa',now(),now());
Query OK, 1 row affected (0.01 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|               94 |
+------------------+
                                                        mysql> SELECT LAST_INSERT_ID();
                                                        +------------------+
                                                        | LAST_INSERT_ID() |
                                                        +------------------+
                                                        |                0 |
                                                        +------------------+
                                                        1 row in set (0.00 sec)
mysql> SELECT id FROM parents ORDER BY id DESC LIMIT 1;
+----+
| id |
+----+
| 94 |
+----+
                                                        mysql> insert into parents(name, created_at, updated_at) values('aaa',now(),now());
                                                        Query OK, 1 row affected (0.00 sec)

                                                        mysql> SELECT LAST_INSERT_ID();
                                                        +------------------+
                                                        | LAST_INSERT_ID() |
                                                        +------------------+
                                                        |               95 |
                                                        +------------------+
                                                        1 row in set (0.00 sec)

mysql> SELECT id FROM parents order by id desc limit 1;
+----+
| id |
+----+
| 95 |
+----+

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|               94 |
+------------------+

欲しかったものはこれだ。

Railsでやってるのは何？

（コードリーディング間違えてるかもなので、興味がある方はソース見てみてください）

# lib/activerecord/connection_adapters/abstract/database_statements.rb:156
#
# Executes an INSERT query and returns the new record's ID
#
# +id_value+ will be returned unless the value is +nil+, in
# which case the database will attempt to calculate the last inserted
# id and return that value.
#
# If the next id was calculated in advance (as in Oracle), it should be
# passed in as +id_value+.
def insert(arel, name = nil, pk = nil, id_value = nil, sequence_name = nil, binds = [])
    sql, binds = to_sql_and_binds(arel, binds)
    value = exec_insert(sql, name, binds, pk, sequence_name)
    id_value || last_inserted_id(value)
end

last_inserted_idが怪しい

# lib/activerecord/connection_adapters/mysql/database_statements.rb:156
def last_inserted_id(result)
    @connection.last_id
end

last_idが怪しい


def connect
    @connection = self.class.new_client(@config)
    configure_connection
end

def new_client(config)
    Mysql2::Client.new(config)
        ~~~
end

mysql2のgemに飛ぶ。
https://github.com/brianmario/mysql2/blob/master/ext/mysql2/client.c:1026

/* call-seq:
 *    client.last_id
 *
 * Returns the value generated for an AUTO_INCREMENT column by the previous INSERT or UPDATE
 * statement.
 */
static VALUE rb_mysql_client_last_id(VALUE self) {
  GET_CLIENT(self);
  REQUIRE_CONNECTED(wrapper);
  return ULL2NUM(mysql_insert_id(wrapper->client));
}

あった。
おんなじことしてそう。

実験（並行で処理）

並行でリクエストきてる様子をみてみる。

  # 先に実行して、トランザクションで処理を遅らせる。
  def test1
    families = (1..10).map do |i|
      [Parent.new(name: "name_#{i}"), (1..2).map { |j| Child.new(name: "name_#{i}_#{j}") }]
    end

    parents = families.map { |p, _| p }
    children = families.flat_map { |_, c| c }
    p Time.current
    ActiveRecord::Base.transaction do
      Parent.import(parents)
      (1..5).each { |i| p i; sleep 1 }
      ids = fetch_last_insert_ids
      related_children = make_relation(ids, children)
      Child.import(related_children)
    end
    p Time.current
  end

  # 後に実行する
  def test2
    families = (21..25).map do |i|
      [Parent.new(name: "name_#{i}"), (1..2).map { |j| Child.new(name: "name_#{i}_#{j}") }]
    end
    parents = families.map { |p, _| p }
    children = families.flat_map { |_, c| c }
    p Time.current
    ActiveRecord::Base.transaction do
      Parent.import(parents)
      ids = fetch_last_insert_ids
      related_children = make_relation(ids, children)
      Child.import(related_children)
    end
    p Time.current
  end

  # 親子の関係を作る
  def make_relation(ids, children)
    relation = 0
    ids.each do |id|
      2.times.each do |_|
        children[relation].parent_id = id
        relation += 1
      end
    end
    children
  end

  # 重要な確認箇所
  def fetch_last_insert_ids
    query = 'SELECT LAST_INSERT_ID() AS id, ROW_COUNT() as row'
    id, row = ActiveRecord::Base.connection.select_one(query).values

    row.times.map { |num| id + num }
  end

irb(main):019:0> Test.test1
Fri, 27 Aug 2021 15:57:42.920741000 JST +09:00
  TRANSACTION (0.2ms)  BEGIN
  Parent Create Many (6.2ms)  INSERT INTO `parents` (`id`,`name`,`created_at`,`updated_at`) VALUES (NULL,'name_1','2021-08-27 15:57:42','2021-08-27 15:57:42'),(NULL,'name_2','2021-08-27 15:57:42','2021-08-27 15:57:42'),(NULL,'name_3','2021-08-27 15:57:42','2021-08-27 15:57:42'),(NULL,'name_4','2021-08-27 15:57:42','2021-08-27 15:57:42'),(NULL,'name_5','2021-08-27 15:57:42','2021-08-27 15:57:42'),(NULL,'name_6','2021-08-27 15:57:42','2021-08-27 15:57:42'),(NULL,'name_7','2021-08-27 15:57:42','2021-08-27 15:57:42'),(NULL,'name_8','2021-08-27 15:57:42','2021-08-27 15:57:42'),(NULL,'name_9','2021-08-27 15:57:42','2021-08-27 15:57:42'),(NULL,'name_10','2021-08-27 15:57:42','2021-08-27 15:57:42')

# 5秒間待ち

                                        irb(main):027:0> Test.test2
                                        Fri, 27 Aug 2021 15:57:44.068953000 JST +09:00
                                        TRANSACTION (0.3ms)  BEGIN
                                        Parent Create Many (0.4ms)  INSERT INTO `parents` (`id`,`name`,`created_at`,`updated_at`) VALUES (NULL,'name_21','2021-08-27 15:57:44','2021-08-27 15:57:44'),(NULL,'name_22','2021-08-27 15:57:44','2021-08-27 15:57:44'),(NULL,'name_23','2021-08-27 15:57:44','2021-08-27 15:57:44'),(NULL,'name_24','2021-08-27 15:57:44','2021-08-27 15:57:44'),(NULL,'name_25','2021-08-27 15:57:44','2021-08-27 15:57:44')
                                        (0.9ms)  SELECT LAST_INSERT_ID() AS id, ROW_COUNT() as row
parent_idが106からの連番になっている
                                        Child Create Many (1.9ms)  INSERT INTO `children` (`id`,`name`,`parent_id`,`created_at`,`updated_at`) VALUES (NULL,'name_21_1',106,'2021-08-27 15:57:44','2021-08-27 15:57:44'),(NULL,'name_21_2',106,'2021-08-27 15:57:44','2021-08-27 15:57:44'),(NULL,'name_22_1',107,'2021-08-27 15:57:44','2021-08-27 15:57:44'),(NULL,'name_22_2',107,'2021-08-27 15:57:44','2021-08-27 15:57:44'),(NULL,'name_23_1',108,'2021-08-27 15:57:44','2021-08-27 15:57:44'),(NULL,'name_23_2',108,'2021-08-27 15:57:44','2021-08-27 15:57:44'),(NULL,'name_24_1',109,'2021-08-27 15:57:44','2021-08-27 15:57:44'),(NULL,'name_24_2',109,'2021-08-27 15:57:44','2021-08-27 15:57:44'),(NULL,'name_25_1',110,'2021-08-27 15:57:44','2021-08-27 15:57:44'),(NULL,'name_25_2',110,'2021-08-27 15:57:44','2021-08-27 15:57:44')
                                        TRANSACTION (5.0ms)  COMMIT
                                        Fri, 27 Aug 2021 15:57:44.137688000 JST +09:00
# 5秒間待終わり
   (0.3ms)  SELECT LAST_INSERT_ID() AS id, ROW_COUNT() as row
  Child Create Many (0.5ms)  INSERT INTO `children` (`id`,`name`,`parent_id`,`created_at`,`updated_at`) VALUES (NULL,'name_1_1',96,'2021-08-27 15:57:47','2021-08-27 15:57:47'),(NULL,'name_1_2',96,'2021-08-27 15:57:47','2021-08-27 15:57:47'),(NULL,'name_2_1',97,'2021-08-27 15:57:47','2021-08-27 15:57:47'),(NULL,'name_2_2',97,'2021-08-27 15:57:47','2021-08-27 15:57:47'),(NULL,'name_3_1',98,'2021-08-27 15:57:47','2021-08-27 15:57:47'),(NULL,'name_3_2',98,'2021-08-27 15:57:47','2021-08-27 15:57:47'),(NULL,'name_4_1',99,'2021-08-27 15:57:47','2021-08-27 15:57:47'),(NULL,'name_4_2',99,'2021-08-27 15:57:47','2021-08-27 15:57:47'),(NULL,'name_5_1',100,'2021-08-27 15:57:47','2021-08-27 15:57:47'),(NULL,'name_5_2',100,'2021-08-27 15:57:47','2021-08-27 15:57:47'),(NULL,'name_6_1',101,'2021-08-27 15:57:47','2021-08-27 15:57:47'),(NULL,'name_6_2',101,'2021-08-27 15:57:47','2021-08-27 15:57:47'),(NULL,'name_7_1',102,'2021-08-27 15:57:47','2021-08-27 15:57:47'),(NULL,'name_7_2',102,'2021-08-27 15:57:47','2021-08-27 15:57:47'),(NULL,'name_8_1',103,'2021-08-27 15:57:47','2021-08-27 15:57:47'),(NULL,'name_8_2',103,'2021-08-27 15:57:47','2021-08-27 15:57:47'),(NULL,'name_9_1',104,'2021-08-27 15:57:47','2021-08-27 15:57:47'),(NULL,'name_9_2',104,'2021-08-27 15:57:47','2021-08-27 15:57:47'),(NULL,'name_10_1',105,'2021-08-27 15:57:47','2021-08-27 15:57:47'),(NULL,'name_10_2',105,'2021-08-27 15:57:47','2021-08-27 15:57:47')
  TRANSACTION (0.5ms)  COMMIT
Fri, 27 Aug 2021 15:57:48.032617000 JST +09:00
parent_idが96からの連番になっている

irb(main):020:0> Parent.find(100)
  Parent Load (3.7ms)  SELECT `parents`.* FROM `parents` WHERE `parents`.`id` = 100 LIMIT 1
=> #<Parent id: 100, name: "name_5", created_at: "2021-08-27 15:57:42.000000000 +0900", updated_at: "2021-08-27 15:57:42.000000000 +0900">
irb(main):021:0> Parent.find(100).children
  Parent Load (0.4ms)  SELECT `parents`.* FROM `parents` WHERE `parents`.`id` = 100 LIMIT 1
  Child Load (4.4ms)  SELECT `children`.* FROM `children` WHERE `children`.`parent_id` = 100 /* loading for inspect */ LIMIT 11
=> #<ActiveRecord::Associations::CollectionProxy [#<Child id: 179, name: "name_5_1", parent_id: 100, created_at: "2021-08-27 15:57:47.000000000 +0900", updated_at: "2021-08-27 15:57:47.000000000 +0900">, #<Child id: 180, name: "name_5_2", parent_id: 100, created_at: "2021-08-27 15:57:47.000000000 +0900", updated_at: "2021-08-27 15:57:47.000000000 +0900">]>

nameをみた感じだと紐付けも期待通り

注意点

InnoDB では AUTO_INCREMENT カラムを含むテーブルへの挿入を行う際に、AUTO-INC ロックと呼ばれる特殊なテーブルレベルロックが使用されます。このロックは通常、指定された一連の INSERT ステートメントに予測可能かつ繰り返し可能な順序で自動インクリメント番号が割り当てられるように、(トランザクションが終了するまでではなく) ステートメントが終了するまで保持されます。

と書いてあるので、一つのinsertステートメント内であれば、連番は問題なさそう

SELECT 以外の DML ステートメント: 影響を受ける行数です。これは、(以前と同様に) UPDATE、INSERT、DELETE などのステートメントに適用されますが、ALTER TABLE や LOAD DATA INFILE などのステートメントにも適用されるようになりました。

とあるので、insert直後に行うことに気をつけなければいけません。

また、

innodb_autoinc_lock_mode = 2 (「インターリーブ」 ロックモード)
このロックモードでは、テーブルレベル AUTO-INC ロックを使用する 「INSERT のような」ステートメントは 1 つも存在しない
任意のステートメントによって挿入される行に対して生成された値が連続的でない可能性があります。

SELECT @@innodb_autoinc_lock_mode;

で値を要チェックしてください

結論

mysqlでbulk流すときは

SELECT LAST_INSERT_ID() AS id, ROW_COUNT() as row

が使えそう！

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0