用代码实现凭借成下列sql语句即可同时批量更新多条数据
UPDATE table_name SET column = CASE 'id' WHEN 1 THEN ? WHEN 2 THEN ? ELSE column END WHERE id IN (1,2)

テーブル作成

カラムとデータ型を指定する。

CREATE TABLE cats
  (
    name VARCHAR(100),
    age INT
  );

テーブル一覧

SHOW TABLES;

カラム一覧

SHOW COLUMNS FROM tablename;

DESC tablename;

DESC cats;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(100) | YES  |     | NULL    |       |
| age   | int(11)      | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.11 sec)

テーブル削除

DROP TABLE tablename;

---

mysql> exit
Bye

エラー

/var/log/mysql/error.log

[Warning] [MY-010139] [Server] Changed limits: max_open_files: 1024 (requested 8161)
[Warning] [MY-010142] [Server] Changed limits: table_open_cache: 431 (requested 4000)
[Warning] [MY-010091] [Server] Can't create test file /var/lib/mysql/mysqld_tmp_file_case_insensitive_test.lower-test
[System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.19) starting as process 18977
[Warning] [MY-010091] [Server] Can't create test file /var/lib/mysql/mysqld_tmp_file_case_insensitive_test.lower-test
[Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /var/lib/mysql/ is case insensitive
[ERROR] [MY-010846] [Server] MYSQL_BIN_LOG::open_purge_index_file failed to open register file.
[ERROR] [MY-010817] [Server] MYSQL_BIN_LOG::open_index_file failed to sync the index file.
[ERROR] [MY-010119] [Server] Aborting
[System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.19)  MySQL Community Server - GPL.
mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

解決策

sudo chmod 777 /var/lib/mysql/
sudo service mysql start

本記事を読むその前に

本記事は事象を分かりやすくお伝えするため、サンプルアプリを題材として執筆しております。
実際は大規模システムにおいて大量の初期データを準備する必要があり、
その際に直面した問題から本記事を書くに至りました。
（@jnchitoさん、ご指摘いただきありがとうございます?‍♂️）

結論

ユニーク制約のあるテーブルにバルクインサートを繰り返すと
ID（主キー）が飛び飛びになることがある。

⬆︎ mahjan_piesテーブルのIDが飛び飛びに、、、。
⬇︎ こんな結果を期待してたのに。

何をしたいのか

麻雀の座席をランダムに決めるための簡単なプログラムを考える。

麻雀では【東南西北中】の中から1つを選び、引いた牌通りの場所に座る。
【東南西北】は下図のように席に座り麻雀を打つことが出来るが、中は見学となる。
（1半荘休み）

そのため、序盤は中が嫌悪されるが、後半は誰もが中を懇願することとなる。
（徹麻による体力の限界）

ちなみにusersテーブルに登録する名前は俺の大学時代の友達の名前だ。
麻雀に明け暮れた結果、4人/5人が留年する結果となった?‍♂️?️?

やってみる

前提

・Rails6系でアプリを作ってある
・バルクインサートにはinsert_allメソッドを使う
・activerecord-importというgemを導入してある

簡単なテーブル構造

今回登場するテーブルは2つ。
usersテーブル?‍♂️とmahjan_piesテーブル?️だ。

mahjan_piesテーブルはuser_idを外部キーとして持っている。
また、nameカラムとuser_idカラムはどちらもユニーク制約が設定されている。

usersテーブル
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | bigint       | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255) | YES  |     | NULL    |                |
| created_at | datetime(6)  | NO   |     | NULL    |                |
| updated_at | datetime(6)  | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+


mahjan_piesテーブル
+------------+--------------+------+-----+---------+----------------+
| id         | bigint       | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255) | YES  | UNI | NULL    |                |
| user_id    | bigint       | YES  | UNI | NULL    |                |
| created_at | datetime(6)  | NO   |     | NULL    |                |
| updated_at | datetime(6)  | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

Seedファイルにロジックを書く

席決めのプログラムはseedファイルに記述し、初期データとして登録させる。

これは説明するより見た方が早い。
コードは以下の通り。

db/seeds.rb

def pies
  %w(東 南 西 北 中) - MahjanPie.pluck(:name)
end

def user_ids
  [*1..5] - MahjanPie.pluck(:user_id)
end

def just_now
  Time.zone.now
end

def seat_position
  { name:       pies.sample,
    user_id:    user_ids.sample,
    created_at: just_now,
    updated_at: just_now }
end

User.insert_all([
  { name: 'わたなべ', created_at: just_now, updated_at: just_now },
  { name: 'さかい',   created_at: just_now, updated_at: just_now },
  { name: 'とし',    created_at: just_now, updated_at: just_now },
  { name: 'つとむ',   created_at: just_now, updated_at: just_now },
  { name: 'じゅーり', created_at: just_now, updated_at: just_now }
])

while MahjanPie.count < 5 do
  MahjanPie.insert_all(
    (5 - MahjanPie.count).times.map { seat_position }
  )

  puts "====== バルクインサート終了... ======"
  puts "座席、もしくは見学が決まっているのは#{MahjanPie.count}人だ"

  return puts "さて、今夜飛ぶのは誰かな？" if MahjanPie.count >= 5

  puts "残る牌は【#{pies.join(' ')}】のみ！"

  sleep(1.5) && puts('...')
  sleep(1.5) && puts('..')
  sleep(1.5) && puts('.')
end

いざ実行

seedファイルを実行するとこのような実行結果が得られた。

terminal

$ bundle exec rails db:seed

terminal

====== バルクインサート終了... ======
座席、もしくは見学が決まっているのは2人だ
残る牌は【東 南 中】のみ！
...
..
.
====== バルクインサート終了... ======
座席、もしくは見学が決まっているのは3人だ
残る牌は【東 南】のみ！
...
..
.
====== バルクインサート終了... ======
座席、もしくは見学が決まっているのは4人だ
残る牌は【東】のみ！
...
..
.
====== バルクインサート終了... ======
座席、もしくは見学が決まっているのは5人だ
さて、今夜飛ぶのは誰かな？

そしてテーブルの中身は以下の通り

terminal

usersテーブル
+----+--------------+----------------------------+----------------------------+
| id | name         | created_at                 | updated_at                 |
+----+--------------+----------------------------+----------------------------+
|  1 | わたなべ       | 2021-03-04 07:37:56.005154 | 2021-03-04 07:37:56.005172 |
|  2 | さかい         | 2021-03-04 07:37:56.005176 | 2021-03-04 07:37:56.005179 |
|  3 | とし           | 2021-03-04 07:37:56.005181 | 2021-03-04 07:37:56.005183 |
|  4 | つとむ         | 2021-03-04 07:37:56.005186 | 2021-03-04 07:37:56.005187 |
|  5 | じゅーり        | 2021-03-04 07:37:56.005190 | 2021-03-04 07:37:56.005191 |
+----+--------------+----------------------------+----------------------------+


mahjan_piesテーブル
+----+------+---------+----------------------------+----------------------------+
| id | name | user_id | created_at                 | updated_at                 |
+----+------+---------+----------------------------+----------------------------+
|  1 | 北   |       2 | 2021-03-04 07:37:56.018787 | 2021-03-04 07:37:56.018797 |
|  2 | 西   |       1 | 2021-03-04 07:37:56.019929 | 2021-03-04 07:37:56.019937 |
|  6 | 中   |       5 | 2021-03-04 07:38:00.541059 | 2021-03-04 07:38:00.541073 |
|  9 | 南   |       4 | 2021-03-04 07:38:05.092544 | 2021-03-04 07:38:05.092582 |
| 11 | 東   |       3 | 2021-03-04 07:38:09.618758 | 2021-03-04 07:38:09.618771 |
+----+------+---------+----------------------------+----------------------------+

つまりこのような席順になった。
（じゅーり、ドンマイ?）

これでいよいよ麻雀を打てるわけだが、ちょっと待ってほしい。

テーブルに登録されたレコードをもう一度よく見てると、
バルクインサートをしたmahjan_piesテーブルのidが連続していない?
これは一体、、、???？？？

発行されたクエリを確認する（SQL）

Railsの開発環境でのログは log/development.log に記載されているので確認する。

log/development.log

INSERT INTO
  `users` (`name`, `created_at`, `updated_at`)
VALUES
  ('わたなべ', '2021-03-04 07:36:05.037216', '2021-03-04 07:36:05.037305'),
  ('さかい', '2021-03-04 07:36:05.037323', '2021-03-04 07:36:05.037334'),
  ('とし', '2021-03-04 07:36:05.037343', '2021-03-04 07:36:05.037351'),
  ('つとむ', '2021-03-04 07:36:05.037359', '2021-03-04 07:36:05.037366'),
  ('じゅーり', '2021-03-04 07:36:05.037374', '2021-03-04 07:36:05.037382')
ON DUPLICATE KEY UPDATE
  `name` = `name`

これはusersテーブルにデータを登録する際のクエリだ。
ちゃんと登録もされている。

次は問題のmahjan_piesテーブルに対するクエリを確認する。

log/development.log

INSERT INTO
  `mahjan_pies` (`name`, `user_id`, `created_at`, `updated_at`)
VALUES
  ('北', 2, '2021-03-04 07:37:56.018787', '2021-03-04 07:37:56.018797'),
  ('西', 1, '2021-03-04 07:37:56.019929', '2021-03-04 07:37:56.019937'),
  ('北', 1, '2021-03-04 07:37:56.020953', '2021-03-04 07:37:56.020958'),
  ('南', 1, '2021-03-04 07:37:56.021981', '2021-03-04 07:37:56.021987'),
  ('東', 2, '2021-03-04 07:37:56.023049', '2021-03-04 07:37:56.023053') 
ON DUPLICATE KEY UPDATE
  `name` = `name`


INTO
  `mahjan_pies` (`name`, `user_id`, `created_at`, `updated_at`)
VALUES
  ('中', 5, '2021-03-04 07:38:00.541059', '2021-03-04 07:38:00.541073'),
  ('中', 5, '2021-03-04 07:38:00.543138', '2021-03-04 07:38:00.543150'),
  ('南', 5, '2021-03-04 07:38:00.546174', '2021-03-04 07:38:00.546210')
ON DUPLICATE KEY UPDATE
  `name` = `name`


INSERT INTO
  `mahjan_pies` (`name`, `user_id`, `created_at`, `updated_at`)
VALUES
  ('南', 4, '2021-03-04 07:38:05.092544', '2021-03-04 07:38:05.092582'),
  ('南', 3, '2021-03-04 07:38:05.094148', '2021-03-04 07:38:05.094162')
ON DUPLICATE KEY UPDATE
  `name` = `name`


INSERT INTO
  `mahjan_pies` (`name`, `user_id`, `created_at`, `updated_at`)
VALUES
  ('東', 3, '2021-03-04 07:38:09.618758', '2021-03-04 07:38:09.618771')
ON DUPLICATE KEY UPDATE
  `name` = `name`

合計で4回のクエリが発行されていることが分かる。
ここでも結論を先に述べてしまうが、 ON DUPLICATE KEY UPDATE によりIDに抜けが出来てしまっているのだ。

ON DUPLICATE KEY UPDATE構文（SQL）

MySQLの公式ドキュメントにしっかりと記載されていた。
ユニーク制約に引っ掛かるデータは登録されないが、IDは増やしますぜ旦那、と。
※詳しくはリンク先に飛んで読んでほしいが、該当箇所は以下の通り。

自動インクリメントカラム、つまりID（主キー）だ。。

ON DUPLICATE KEY UPDATE 構文を使えばユニーク制約が破られることはないが、
ユニーク制約に引っかかったデータの分だけIDが加算されてしまうらしい。。
何てこったい。

つまりこういうこと

各クエリごとに切り分けて見ていく。
最初のクエリでは5つのレコードを登録しようとしている。

ただし、nameカラムとuser_idカラムにユニーク制約がかかっているため、
登録出来たのは最初の2つのレコードだけ。ID 1, 2が振られる。

残り3つのレコードは登録出来ず、かつIDも加算されてしまっている。
そのためID 3, 4, 5は使えなくなった。

次のクエリは3つのレコードを登録しようとしている。
最初のレコードはユニーク制約を受けず登録出来た。これがID 6になる。
残り2つのレコードは登録出来ず、かつID 7, 8も使えなくなった。

次のクエリは2つのレコードを登録しようとしている。
最初のレコードは登録できた。これがID 9。
残り1つのレコードは登録出来ず、ID 10も使えなくなった。

そして最後に登録されたレコードがID 11。
先ほども示したが、その結果がこれになるわけだ。なるほど理解できた。

terminal

mahjan_piesテーブル
+----+------+---------+----------------------------+----------------------------+
| id | name | user_id | created_at                 | updated_at                 |
+----+------+---------+----------------------------+----------------------------+
|  1 | 北   |       2 | 2021-03-04 07:37:56.018787 | 2021-03-04 07:37:56.018797 |
|  2 | 西   |       1 | 2021-03-04 07:37:56.019929 | 2021-03-04 07:37:56.019937 |
|  6 | 中   |       5 | 2021-03-04 07:38:00.541059 | 2021-03-04 07:38:00.541073 |
|  9 | 南   |       4 | 2021-03-04 07:38:05.092544 | 2021-03-04 07:38:05.092582 |
| 11 | 東   |       3 | 2021-03-04 07:38:09.618758 | 2021-03-04 07:38:09.618771 |
+----+------+---------+----------------------------+----------------------------+

activerecord-import も同様

Rails5系で主流だったgem、activerecord-import でも同様の事象が発生してしまう。
参考までに activerecord-import を使う場合、コードはこのようになる。

db/seeds.rb

while MahjanPie.count < 5 do
- MahjanPie.insert_all(
-     (5 - MahjanPie.count).times.map { seat_position }
+ MahjanPie.import(     
+     (5 - MahjanPie.count).times.map { seat_position },
+     on_duplicate_key_ignore: true
  )

さいごに

そもそもユニーク制約に引っ掛かるようなクエリ発行するのがアカンやろ！というご指摘はもっともです。
が！！IDが飛び飛びにならずにバルクインサートできる方法を知っている方！
もしいたらぜひ教えてください?

結構時間取られたので備忘録

結論

1. すでにインストール済みの MySQL Installer のアンインストール
2. 2つ目のインストーラ（mysql-installer-community-8.0.23.0.msi）からインストール

自分の場合、以前 MySQL Workbanch のインストールのため MySQL Installer を使用していたので、「設定」→「アプリと機能」からアンインストールしてから試すとエラーなく進めました。

参考記事

エラーメッセージと発生箇所

MySQL上でdumpファイルを読み込もうとした時に発生。

ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

このエラーが起きた背景

MySQLサーバー上に複数のdatabaseが存在し、そのうちの一つのdatabaseがうっかりdropされてしまった。Staging環境だったのでデータについては神経質にならずに一日前に取得していたmysqldumpから復旧しようとしたが、なぜか取得したmysqldumpを流そうとしたら表題のエラーが生じた。

流そうとしたmysqldumpの取得はdatabase別に行われていた。そのため、消えたdatabaseにだけだけ、dumpファイルからimportを行おうとした。
mysqlのユーザーは、普段このdatabaseの読み書きを行うアプリケーションで使用しているユーザーを使用した。

ちなみに、エラーが出たときには気づいていなかったが、mysqldumpを取得した時には下記の警告メッセージが出ていた。

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 

エラーが起きた原因

mysqldumpを実行した時に--set-gtid-purged=OFFオプションをつけていなかった。これによって取得されたdumpファイルの中にGTIDに関するコードが入り込んでおり、部分的にdatabaseを更新する際に表題のエラーが生じた。

暫定対処

何をするかの概要

とりあえずエラーを解消してデータのリストアをするために、mysqldumpからGTIDに関するコードを消してから、インポートを行う。

GTIDに関する記述を消す

dumpファイルの上の方の記述を消す

dumpファイルの上の方にある下記の行を全て消す。コメントは別に消さなくても良い。

-- SETの3行を全部消す。

SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;

--
-- GTID state at the beginning of the backup 
--

SET @@GLOBAL.GTID_PURGED='';

dumpファイルの下の方の記述を消す

-- この行を消す
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

dumpファイルを流し込む

エラーが生じなくなるので、データを流し込む。もしここで流し込めなかったら、この記事で説明するのとは別の原因でエラーが発生している可能性がある。

恒久対処

何をするかの概要

部分的にmysqldumpを取得する場合は、--set-gtid-purged=OFFオプションをつけるようにする。

--set-gtid-purged=OFF の効果

--set-gtid-purged=OFFオプションをつけた時とつけない時でdumpファイルの差分を出すと、「暫定対処」で消した部分にだけ差分が出ていることが分かる。

上の方。

下の方。（一番下のはただのコメント）

参考

• https://support.acquia.com/hc/en-us/articles/360056635953-Troubleshooting-ERROR-1227-42000-at-line-18-Access-denied-you-need-at-least-one-of-the-SUPER-privilege-s-for-this-operation-

CHAR と VARCHAR

文字列型の主要なものとして、CHAR と VARCHARが挙げられる。

char(128)
varchar(64)

後ろのカッコにバイト数を指定できる。

CHAR

データは固定長。

char(100)

上記のように指定した場合、与えた文字列が100バイトに満たない場合は空白文字が末尾に追加され強制的に100バイトになる。

0 ~ 255 まで指定できる。

末尾に追加された空白文字は、取り出し時に自動的に取り除かれる。もともと末尾に空白文字を含むデータでも、同じように空白は取り除かれる。

VARCHAR

データは可変長。

char(100)

100バイトまで受け付けるのはもちろんのこと、１バイトの文字列を与えた場合はそのまま１バイト。

0 ~ 65,535 まで指定できる。

どちらを使うか？

基本的には VARCHARで良い。

CHARの方が早いという意見もあるが、大した差はないとする意見もある。
ちなみにMySQL公式ドキュメントには、特に性能差についての言及は見当たらなかった。

はじめに・・・

今回はPHPをherokuを使用してデプロイするにあたって苦労した点を書いていきたいと思います。苦労した部分はたくさんありますが、まずは注意が必要なデータベースの設定について記事を書いていきたいと思います。基本的な設定に関して今回は詳しい手順を省略していますがよろしくお願いします。

開発環境

まずは今回作成にあたって利用したものです。
php(バージョン7.4.12)＋MySQL
Heroku(ユーザー登録が必要です)
Cleardb(herokuのオプション、これがあればMySQLが使えます)
MAMP(PHPの開発環境、今回の話ではあまり関係ありません)

これらを用いてアプリ開発に取り組んでいました。ちなみにherokuでは通常PostgreSQLを用いますが、Cleardbというものを使えばMySQLが利用できます。
ただしクレジットカードの登録が必須になります。(無料枠自体はありますし急に課金されることはないようですが、それであっても登録が必須)
herokuでMySQLを使用する詳しい手順は記事にしようとは思っているのですが、参照したサイトがあるのでそちらを紹介します。ちなみに僕はRailsのアプリもMySQLでデプロイしました。
https://trialanderror.jp/heroku-mysql-start/

データベースの情報取得

MAMPを利用し、ローカル環境でデータベースに接続する場合は、以前書いた記事のコードで接続を行えます。
https://qiita.com/tetsu_19920922/items/f156c2bec2448eda0d2f
一方本番環境の場合は、herokuのcleardbの情報を元にコードを書き直し、接続する必要があります。
cleardbの情報を取得するには以下のようにターミナルで入力します。(herokuにデプロイする予定のアプリを指定した状態で入力してください)

$ heroku config | grep CLEARDB_DATABASE_URL

これを入力すると以下のような形で出力されます。

CLEARDB_DATABASE_URL: mysql://ユーザー名:パスワード@ホスト名/データベース名?reconnect=true

ホスト名は最後にcleardb.netがついてきますし、データベース名はherokuから始まると思います。パスワードは8桁になっていると思います。

デプロイ、その前に！

それではこの取得したデータを元にデータベースの接続文を変更・・・となるわけですが、herokuを用いたデプロイにはgitとの連携が不可欠です。
gitにそのままデータベースのユーザー名やパスワードが載ったものをコミットしてしまうと不正に操作されたり、改竄されたりとよくないことがたくさん起こり、ひいてはアプリに大きな障害を来す可能性があります。

そこで環境変数を用いることで、gitには見えない形でパスワードやユーザー名を登録し、そこから参照という形をとりましょう！
環境変数を用いる方法はphpであればphpdotenvを使用するというものもありますが、本番環境での利用はあまり推奨していないのと、gitの設定を忘れると結局コミットされてしまう（のと筆者があまり使い方を理解できなかった・・・)ので、今回はherokuで環境変数を設定する方法をとります。

herokuに環境変数を入れ込む

heroku config:addの後ユーザー名であれば　DB_USERNAME＝'<ユーザー名>'と言う形でターミナルに入れていきます。もちろんこのユーザー名は上で書いた内容で参照したものを入れてください。具体的に言うと

$ heroku config:add DB_USERNAME='<ユーザー名>'

$ heroku config:add DB_PASSWORD='<パスワード>'

$ heroku config:add DB_NAME='<データベース名>'

$ heroku config:add DB_HOSTNAME='<ホスト名>'

$ heroku config:add DB_PORT='3306'

これを入れていきましょう。ポートはherokuでMySqlを使用する場合3306が開放されているようなのでそれを用いましょう。またユーザー名とパスワードさえ環境変数に入れておけば安全だと思いますが、データベース名、ホスト名も入れてしまってもいいと思います。
入れた環境変数を確認するには、heroku configのみをターミナルで入力してください。削除はheroku config:removeコマンドで可能です。

データベースへの接続

あとはデータベースの接続を書き換えていくのみです。herokuの環境変数はgetenvで利用できます。

$user=getenv('DB_USERNAME');
$password=getenv('DB_PASSWORD');

データベース名やホスト名も書き換えても良いでしょう。このように書き換えていけば、gitにパスワードやユーザーネームがアップされることなく、本番環境でデータベースに接続できます！

最後に・・・

初学者なので理解が不足している部分もあるかもしれませんので、何かありましたらご指摘よろしくお願いします。