(VARBINARYの代わりとしての)COLLATE utf8mb4_bin

いやまぁそのまんまなのですが。

ちょいと故ありましてLaravel触ってるんですが、あの子、デフォでVARBINARY扱う手段ないんですよねぇ……。

DB::statement('ALTER TABLE (以下略

とかで「可能」なのは理解しているんですが、それも「ど～なのよ？」的な。

で。
おいちゃんがVARBINARYを使いたい、一つは「utf8mb4で文字列の長さがINDEXとかPKとか*1」なんですが。
もう一つが「大文字と小文字を等しいとかマジですか？ 馬鹿なの？ ○○の？」ってあたり。
gallu.hatenadiary.jp
gallu.hatenadiary.jp
gallu.hatenadiary.jp
辺りをご参照の事。

で、「大文字小文字は違うもの」だけなら「COLLATE utf8mb4_bin」でいける、のと、これだとLaravelでも一応取り扱える、ので、軽く実験君。

用意その1。

CREATE TABLE `t1` (
  `id` SERIAL,
  `email` varchar(255) COLLATE utf8mb4_bin NOT NULL COMMENT 'email',
  UNIQUE KEY `unique_t1_email` (`email`),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='ユーザテーブル';

CREATE TABLE `t2` (
  `id` SERIAL,
  `email` varchar(255) NOT NULL COMMENT 'email',
  UNIQUE KEY `unique_t2_email` (`email`),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ユーザテーブル';

実験。

INSERT INTO t1(email) VALUES('gallu@example.com');
INSERT INTO t1(email) VALUES('Gallu@example.com');

INSERT INTO t2(email) VALUES('gallu@example.com');
INSERT INTO t2(email) VALUES('Gallu@example.com');

mysql> INSERT INTO t1(email) VALUES('gallu@example.com');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1(email) VALUES('Gallu@example.com');
Query OK, 1 row affected (0.00 sec)

で

mysql> INSERT INTO t2(email) VALUES('gallu@example.com');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2(email) VALUES('Gallu@example.com');
ERROR 1062 (23000): Duplicate entry 'Gallu@example.com' for key 't2.unique_t2_email'

うん予想通り。

一応、再実験くん。

CREATE TABLE `t3` (
  `id` SERIAL,
  `email` varchar(255) COLLATE utf8mb4_bin NOT NULL COMMENT 'email',
  UNIQUE KEY `unique_t3_email` (`email`),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ユーザテーブル';

mysql> INSERT INTO t3(email) VALUES('gallu@example.com');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t3(email) VALUES('Gallu@example.com');
Query OK, 1 row affected (0.00 sec)

うん。
んで、Laravelだと

$table->collation = 'utf8mb4_bin';

で、(カラムには無理だけどテーブルには)貼り付けられる。

という、多分「ってもおいちゃん自身は使うことねぇんだろうなぁ」的Tipsの検証をしたので、一応、めも。

*1:"MySQL INDEX VARCHAR 長さ" とかでググって

MySQLでUNIQUEに引っかかった時のAUTO INCREMENTの挙動

ちとうちの子から

MySQLでユニークキーとPKあるテーブルに対してON DUPLICATE KEY UPDATE張って、uniqueでの重複させるとauto increment値飛んじゃうんだね。。。

という興味深い話を聞いたので、早速実験。

CREATE TABLE test (
    id SERIAL,
    name VARCHAR(256) UNIQUE,
    PRIMARY KEY(id)
);

mysql> insert into test(name) values('n1');
Query OK, 1 row affected (0.02 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | n1   |
+----+------+
1 row in set (0.00 sec)

うんまぁ、ここまでは前提。

んでわ、確認。

mysql> insert into test(name) values('n1');
ERROR 1062 (23000): Duplicate entry 'n1' for key 'name'

mysql> INSERT INTO test (name) values ('n2') ON DUPLICATE KEY UPDATE name='n3';
Query OK, 1 row affected (0.02 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | n1   |
|  3 | n2   |
+----+------+
2 rows in set (0.00 sec)

……あ、ほんどだ飛んでる。

mysql> INSERT INTO test (name) values ('n3') ON DUPLICATE KEY UPDATE name='n4';
Query OK, 1 row affected (0.02 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | n1   |
|  3 | n2   |
|  4 | n3   |
+----+------+
3 rows in set (0.00 sec)

……続けると飛んでないなぁ。

mysql> insert into test(name) values('n1');
ERROR 1062 (23000): Duplicate entry 'n1' for key 'name'
mysql> INSERT INTO test (name) values ('n4') ON DUPLICATE KEY UPDATE name='n5';
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | n1   |
|  3 | n2   |
|  4 | n3   |
|  6 | n4   |
+----+------+
4 rows in set (0.00 sec)

うん、エラーが起きると飛ぶ………はて？

mysql> insert into test(name) values('n1');
ERROR 1062 (23000): Duplicate entry 'n1' for key 'name'
mysql> insert into test(name) values('n1');
ERROR 1062 (23000): Duplicate entry 'n1' for key 'name'
mysql> insert into test(name) values('n1');
ERROR 1062 (23000): Duplicate entry 'n1' for key 'name'
mysql> INSERT INTO test (name) values ('n5') ON DUPLICATE KEY UPDATE name='n6';
Query OK, 1 row affected (0.02 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | n1   |
|  3 | n2   |
|  4 | n3   |
|  6 | n4   |
| 10 | n5   |
+----+------+
5 rows in set (0.00 sec)

あ、回数分とんだｗ

mysql> insert into test(name) values('n1');
ERROR 1062 (23000): Duplicate entry 'n1' for key 'name'
mysql> insert into test(name) values('n1');
ERROR 1062 (23000): Duplicate entry 'n1' for key 'name'
mysql> insert into test(name) values('n6');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO test (name) values ('n7') ON DUPLICATE KEY UPDATE name='n8';
Query OK, 1 row affected (0.02 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | n1   |
|  3 | n2   |
|  4 | n3   |
|  6 | n4   |
| 10 | n5   |
| 13 | n6   |
| 14 | n7   |
+----+------+
7 rows in set (0.00 sec)

うん「UNIQUEでエラーするとエラー回数分飛んで、一回insertするとリセットされる」ぽい。

mysql> insert into test(name) values('n1');
ERROR 1062 (23000): Duplicate entry 'n1' for key 'name'
mysql> insert into test(name) values('n1');
ERROR 1062 (23000): Duplicate entry 'n1' for key 'name'
mysql> insert into test(name) values('n1');
ERROR 1062 (23000): Duplicate entry 'n1' for key 'name'
mysql> insert into test(name) values('n1');
ERROR 1062 (23000): Duplicate entry 'n1' for key 'name'
mysql> insert into test(name) values('n1');
ERROR 1062 (23000): Duplicate entry 'n1' for key 'name'
mysql> insert into test(name) values('n8');
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | n1   |
|  3 | n2   |
|  4 | n3   |
|  6 | n4   |
| 10 | n5   |
| 13 | n6   |
| 14 | n7   |
| 20 | n8   |
+----+------+
8 rows in set (0.00 sec)

しつこく確認＆別に「ON DUPLICATE KEY UPDATE」じゃなくても、飛ぶねぇ……

……あれ？ じゃぁ、PKの時にも飛ぶのん？

mysql> drop table test;
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE test (
    ->     id SERIAL,
    ->     name VARCHAR(256) UNIQUE,
    ->     PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.28 sec)

mysql> insert into test(name) values('n1');
Query OK, 1 row affected (0.02 sec)

mysql> insert into test(name) values('n2');
Query OK, 1 row affected (0.02 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | n1   |
|  2 | n2   |
+----+------+
2 rows in set (0.00 sec)

前提作り直して。

mysql> insert into test(id, name) values(1, 'n3');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into test(id, name) values(1, 'n3');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into test(id, name) values(1, 'n3');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into test(name) values('n3');
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | n1   |
|  2 | n2   |
|  3 | n3   |
+----+------+
3 rows in set (0.00 sec)

ほむ、PKだと飛ばないのか。

mysql> insert into test(name) values('n1');
ERROR 1062 (23000): Duplicate entry 'n1' for key 'name'
mysql> insert into test(name) values('n1');
ERROR 1062 (23000): Duplicate entry 'n1' for key 'name'
mysql> insert into test(name) values('n1');
ERROR 1062 (23000): Duplicate entry 'n1' for key 'name'
mysql> insert into test(name) values('n1');
ERROR 1062 (23000): Duplicate entry 'n1' for key 'name'
mysql> insert into test(name) values('n1');
ERROR 1062 (23000): Duplicate entry 'n1' for key 'name'
mysql> insert into test(name) values('n4');
Query OK, 1 row affected (0.02 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | n1   |
|  2 | n2   |
|  3 | n3   |
|  9 | n4   |
+----+------+
4 rows in set (0.00 sec)

UNIQUEで飛ぶのを確認。

ほむ……まぁ「auto increment、そもそも"連番保証"とかしてないし(確か、MariaDB の Galera Cluster だとノード数に応じて飛んだはずだし)」、ってのはあるんですが。
記憶はしておいて損がないネタ、かも、しれない……誰かよかったら「仕組み側」を書いていただけると喜んで読みに行きますｗｗｗ

0000-00-00 と PHP

MySQLの、(少なくとも一部では)悪名高き 0000-00-00 について、は、そこそこブログがあるのですが。
「それを、PHP(plain)とか(PHPの)フレームワークとかでどうやってるんだろ？」というのが、ざっくりググった限りだと案外と無かったので。
調査かてがて、備忘録として。

0000-00-00 について

まずは 0000-00-00 について。
いやまぁググればあちこちで出てくる「MySQLに固有の、奇異なデータ」なのですが。
端的には
・入れ方によって、日付型(DATE、DATETIME、TIMESTAMP)に入る事があるデータ
・MySQL以外で 0000-00-00 が入るRDBはない(はず)
・NOT NULL制約が入っているカラムで「0000-00-00」があると、WHERE句の IS NULL、IS NOT NULLの双方で 0000-00-00が引っかかる
てなもの、になります。

http://nippondanji.blogspot.com/2018/05/mysqlzero-date.html
https://soudai.hatenablog.com/entry/2018/05/12/191050
http://sakaik.hateblo.jp/entry/20151227/mysql_date_null

など、言及されている所は少なからずあるか、と思います。
詳しくは、上述のBlogなど見て頂くほうが早いかなぁ、と思います。

少し細かくまとめると。

・カラムにNOT NULL制約がある時
　→ 空文字('')をVALUESに与えると、 0000-00-00 になる： modeにSTRICT_TRANS_TABLESがある時はエラーが出る(Incorrect date value: '' for column)
　→ NULLをVALUESに与えると、エラーになる(当たり前だ)
・カラムにNOT NULL制約がない時
　→ 空文字('')をVALUESに与えると、 0000-00-00 になる： modeにSTRICT_TRANS_TABLESがある時はエラーが出る(Incorrect date value: '' for column)
　→ NULLをVALUESに与えると、NULLになる(当たり前だ)

となります。
また
・カラムにNOT NULL制約があり、かつ、DEFAULTが定義されている時
　→ 空文字('')をVALUESに与えると、 0000-00-00 になる： modeにSTRICT_TRANS_TABLESがある時はエラーが出る(Incorrect date value: '' for column)
　→ NULLをVALUESに与えると、エラーになる(当たり前だ)
　→ INSERTの対象カラムからそもそも削除すると、DEFAULTで定義された値になる(当たり前だ)
・カラムにNOT NULL制約がなく、かつ、DEFAULTが定義されている時
　→ 空文字('')をVALUESに与えると、 0000-00-00 になる： modeにSTRICT_TRANS_TABLESがある時はエラーが出る(Incorrect date value: '' for column)
　→ NULLをVALUESに与えると、NULLになる(DEFAULTの値にはならない)
　→ INSERTの対象カラムからそもそも削除すると、DEFAULTで定義された値になる(当たり前だ)

となります。
ここ、後述でちょっと重要になるので、ポイントとして。

「フレームワーク無し」で実装すると？

まぁ昨今だととかですかねぇ。古来だとか。
日付を入力してもらう時に、まぁ最終的には文字列で受け取るわけ、なのですが。
これが「必須入力」なら「日付のフォーマットに沿ってるかどうかを判断」とかでいけばよくて……個人的にはstrtotime()使うのが、楽で好みかなぁ。

$t = strtotime($_POST['日付'] ?? '');
if (false === $t) {
    // エラー処理
    return;
}
// else
$date = date('Y-m-d H:i:s', $t);

とかやるとざっくりとフォーマットが整うので、よく使います。
DateTimeとかDateTimeImmutableとかのクラスを使う場合(このチョイスならDateTimeImmutableのほうが好み)、エラー時は「例外を投げてくる」ので、書き方にちょっとだけ注意を払いましょう。

ただ、必須じゃない場合「空文字を許容」する必要がある、と思われるのですが。

$date = $_POST['日付'] ?? '';
if ('' !== $date) {
    $t = strtotime();
    if (false === $t) {
        // エラー処理
        return;
    }
    // else
    $date = date('Y-m-d H:i:s', $t);
}

上述で「パラメタの値を取得して」「SQL作成してINSERTとかUPDATEとか」やると、結局日付系のカラムで「VALUEが空文字なINSERT/UPDATE」が走るので、0000-00-00問題が、回避できないような気がするのですだよ……。

なお

$date = $_POST['日付'] ?? null;

ってやっても変わらない。だって「formには"日付"のnameアトリビュートが存在しているから、空文字が返ってくる事が十分に想起される」から。

PHPのフレームワークはどうなってるんだろ？

と言うわけで様々なフレームワークを……試す根性がありませんでしたすみません orz
ので、現状多分「一番メジャーであろう」Laravelで実験。
とりあえずバージョンは…… 6.4.1 。あら。5.8系かと思ったんだけど、バージョン指定しないとこんな先のバージョンになるのか。
まぁ「新しいほうがより機能は洗練されてる」だろうから、あんまり気にしない(雑

すげぇ単純に
・テーブル作って
・form入りのテンプレート作って
・ざっくりとinsert
こんな感じ。

マニュアルのPageにも
https://readouble.com/laravel/5.8/ja/eloquent.html

        $flight = new Flight;
        $flight->name = $request->name;
        $flight->save();

ってあるので、おんなじようにやってみる……エラー。
どうもConvertEmptyStringsToNullというのがお邪魔しくさりやがってくださっているらしく
・ConvertEmptyStringsToNull Middleware によって「空文字ならnullに置換」される
・対象カラムがNOT NULL制約
・なので「NOT NULL制約の所にNULLぶち込んだから嫌がられる」
という単純構造。

一端 app/Http/Kernel.php に修正を入れて、ConvertEmptyStringsToNull をoff(ついでにTrimStringsもoffしたほうがいいんじゃねぇか疑惑があるんだが一端放置)。
「未入力ならそのまま空文字になる」ように修正して再度実行。

……予想通り 0000-00-00 が入りやがりました orz
この辺、Laravelでも特に「なんか対応」はしていないんだなぁ、と、実感。

今までよく引っかかってこなかったなぁ……と思うんだけど、まぁぶち合ったんだからどうにか考えませんとなぁ。

https://gallu.hatenadiary.jp/entry/2019/11/04/160910 で、実装について少し考察をしてみます。

おまけ：実験結果

mysql> show create table date_tests;
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| date_tests | CREATE TABLE `date_tests` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`d_test` date NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select id, d_test from date_tests;
+----+------------+
| id | d_test |
+----+------------+
| 1 | 0000-00-00 |
| 2 | 2019-11-01 |
+----+------------+
2 rows in set (0.01 sec)

mysql> insert into date_tests(d_test) value('');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> insert into date_tests(d_test) value(null);
ERROR 1048 (23000): Column 'd_test' cannot be null
mysql> select id, d_test from date_tests;
+----+------------+
| id | d_test |
+----+------------+
| 1 | 0000-00-00 |
| 2 | 2019-11-01 |
| 3 | 0000-00-00 |
+----+------------+
3 rows in set (0.00 sec)

mysql> ALTER TABLE date_tests MODIFY COLUMN `d_test` date ;
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into date_tests(d_test) value('');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into date_tests(d_test) value(null);
Query OK, 1 row affected (0.01 sec)

mysql> select id, d_test from date_tests;
+----+------------+
| id | d_test |
+----+------------+
| 1 | 0000-00-00 |
| 2 | 2019-11-01 |
| 3 | 0000-00-00 |
| 4 | 0000-00-00 |
| 5 | NULL |
+----+------------+
5 rows in set (0.00 sec)

mysql> SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into date_tests(d_test) value('');
ERROR 1292 (22007): Incorrect date value: '' for column 'd_test' at row 1
mysql> insert into date_tests(d_test) value(null);
Query OK, 1 row affected (0.00 sec)

mysql> select id, d_test from date_tests;
+----+------------+
| id | d_test |
+----+------------+
| 1 | 0000-00-00 |
| 2 | 2019-11-01 |
| 3 | 0000-00-00 |
| 4 | 0000-00-00 |
| 5 | NULL |
| 6 | 0000-00-00 |
| 7 | NULL |
+----+------------+
7 rows in set (0.00 sec)

mysql> select id, d_test from date_tests where d_test is null;
+----+--------+
| id | d_test |
+----+--------+
| 5 | NULL |
| 7 | NULL |
+----+--------+
2 rows in set (0.00 sec)

mysql> select id, d_test from date_tests where d_test is not null;
+----+------------+
| id | d_test |
+----+------------+
| 1 | 0000-00-00 |
| 2 | 2019-11-01 |
| 3 | 0000-00-00 |
| 4 | 0000-00-00 |
| 6 | 0000-00-00 |
+----+------------+
5 rows in set (0.00 sec)

mysql> ALTER TABLE date_tests MODIFY COLUMN `d_test` date NOT NULL;
Query OK, 5 rows affected (0.09 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> select id, d_test from date_tests ;
+----+------------+
| id | d_test |
+----+------------+
| 1 | 0000-00-00 |
| 2 | 2019-11-01 |
| 3 | 0000-00-00 |
| 4 | 0000-00-00 |
| 6 | 0000-00-00 |
+----+------------+
5 rows in set (0.00 sec)

mysql> select id, d_test from date_tests where d_test is null;
+----+------------+
| id | d_test |
+----+------------+
| 1 | 0000-00-00 |
| 3 | 0000-00-00 |
| 4 | 0000-00-00 |
| 6 | 0000-00-00 |
+----+------------+
4 rows in set (0.00 sec)

mysql> select id, d_test from date_tests where d_test is not null;
+----+------------+
| id | d_test |
+----+------------+
| 1 | 0000-00-00 |
| 2 | 2019-11-01 |
| 3 | 0000-00-00 |
| 4 | 0000-00-00 |
| 6 | 0000-00-00 |
+----+------------+
5 rows in set (0.00 sec)

mysql> ALTER TABLE date_tests MODIFY COLUMN `d_test` date DEFAULT '1970-1-1';
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into date_tests(d_test) value('');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into date_tests(d_test) value(null);
Query OK, 1 row affected (0.00 sec)

mysql> select id, d_test from date_tests ;
+----+------------+
| id | d_test |
+----+------------+
| 1 | 0000-00-00 |
| 2 | 2019-11-01 |
| 3 | 0000-00-00 |
| 4 | 0000-00-00 |
| 6 | 0000-00-00 |
| 8 | 0000-00-00 |
| 9 | NULL |
+----+------------+
7 rows in set (0.00 sec)

mysql> ALTER TABLE date_tests MODIFY COLUMN `d_test` date NOT NULL DEFAULT '1970-1-1';
Query OK, 7 rows affected, 1 warning (0.02 sec)
Records: 7 Duplicates: 0 Warnings: 1

mysql> select id, d_test from date_tests ;
+----+------------+
| id | d_test |
+----+------------+
| 1 | 0000-00-00 |
| 2 | 2019-11-01 |
| 3 | 0000-00-00 |
| 4 | 0000-00-00 |
| 6 | 0000-00-00 |
| 8 | 0000-00-00 |
| 9 | 0000-00-00 |
+----+------------+
7 rows in set (0.01 sec)

mysql> insert into date_tests(d_test) value('');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into date_tests(d_test) value(null);
ERROR 1048 (23000): Column 'd_test' cannot be null
mysql> select id, d_test from date_tests ;
+----+------------+
| id | d_test |
+----+------------+
| 1 | 0000-00-00 |
| 2 | 2019-11-01 |
| 3 | 0000-00-00 |
| 4 | 0000-00-00 |
| 6 | 0000-00-00 |
| 8 | 0000-00-00 |
| 9 | 0000-00-00 |
| 10 | 0000-00-00 |
+----+------------+
8 rows in set (0.00 sec)

mysql> insert into date_tests(id) value(null);
Query OK, 1 row affected (0.01 sec)

mysql> select id, d_test from date_tests ;
+----+------------+
| id | d_test |
+----+------------+
| 1 | 0000-00-00 |
| 2 | 2019-11-01 |
| 3 | 0000-00-00 |
| 4 | 0000-00-00 |
| 6 | 0000-00-00 |
| 8 | 0000-00-00 |
| 9 | 0000-00-00 |
| 10 | 0000-00-00 |
| 11 | 1970-01-01 |
+----+------------+
9 rows in set (0.01 sec)

database領域の作成とユーザの作成

最近少し、作り方が変わったのか変えたのか。
こんなやり方をしてることが多いです、ってのがあったので備忘録。

create database データベース名;
CREATE USER 'ユーザ名'@'サーバ名' IDENTIFIED BY 'パスワード';
GRANT ALL ON データベース名.* TO 'univcoop'@'サーバ名';

サーバ名は、localhostじゃない場合で適度にワイルドカードしたい場合は「192.168.0.%」とかって書き方で。

あと。
MySQL8.0(.4 ？)から、認証方式のデフォルトがcaching_sha2_passwordってのに変わったそうでございます( https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html )。
via https://qiita.com/ucan-lab/items/3ae911b7e13287a5b917

で、その辺知らずにPDOでガリゴリっと書きますと

SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client

といった感じのエラーに出くわします。

この場合は、ユーザを作る時に

CREATE USER 'ユーザ名'@'サーバ名' IDENTIFIED WITH mysql_native_password BY 'パスワード';

ってな感じで、明示的に指定してやると、よい塩梅になるようでございます。

色々と、地味にあちこち変化はあるなぁ、と思いつつ、備忘録用にメモり。

あ。
いつの頃からかは不明ですが、パスワードが雑だと

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

的に怒られます。
大まかには
・英の大小文字、数字、記号がそれぞれ入って
・全体で８文字以上
という感じのようでございます。

my.cnfのvalidate-passwordとか「SHOW GLOBAL VARIABLES LIKE 'validate%';」で見て取れる値とかで色々あるようですが……まぁ、これくらいはこのままでもいいんじゃないかなぁ、的な。
なので、面倒なんで調べてないんで、興味がある各位におかれましては、自習いただければ幸いです。

多言語対応のあれこれ

ふと生徒さんに質問をいただいたのもあって。
ちょうどよいきっかけになったので、せっかくなんでBlogで。

本質的には「どの言語のどの領域」でもある程度応用が利くかと思われますが。
一応、おいちゃんの記述なんで「MySQLでPHPつかったWebアプリケーション」をど真ん中に据えて、ってな感じで。

まず「ユーザからの入力について多言語対応したい」は簡単で「保存するデータ及びHTMLをUTF-8にしましょう」で、fin。
いやまぁUnicodeであれば大体無問題だと思われるのですが*1。HTMLとかでUTF-16って、あんまり見た記憶がないんですよねぇその辺詳しい諸氏の突っ込み求む。
ひとつポイントがあるとすると「MySQLの文字コードは、utf8じゃなくてutf8mb4」ってあたりなのですが、そのためにはMySQL5.5.3+というバージョンが必要なのでそれより低いバージョンの場合はバージョンをあげましょうここに慈悲はない。

お次に「プログラム的に動的な文字の出力」をどうするか、ですが。例えば「お知らせ」とか。
この辺あたりから少し面倒になるのですがおおむね
・ユーザが「表示してほしい」言語を忖度する
・忖度した結果としての「選択された言語」のデータを引っ張ってくる
ってまぁ、こんな感じ。

まぁ最終的には「ユーザに選んでもらう」でよいですし、選んだ結果は「Cookieあたりに保存」しておけばよいのですが。
初手のアクセスで「きっとこの人は日本語圏の人なのではなかろうか？」を推測したいのであれば、HTTP RequestヘッダのAcecpt-Languageを見ると、比較的、ヒントがあったり。
PHPの場合

$_SERVER['HTTP_ACCEPT_LANGUAGE']

で取得可能。帰ってくる値は、例えば

"ja,en-US;q=0.7,en;q=0.3"

ってな感じなので。初手にjaがあったら「なんとなくこの人、日本語圏の人なのではないだろうか？」と推測が可能。
それ以外は適宜しらべて。en-USとかきたら英語圏だし、それ以外で斜めに調べた限りだと「de (ドイツ語)」「es (スペイン語)」「it (イタリア語)」など。
後ろの「q=0.7,en;q=0.3」にも本来的には意味があるので、興味がある諸氏は適宜しらべたし(大まかには、各言語の優先確率)。
雑に行くんなら「先頭２文字で判断」でも、当面は困らないんじゃないかなぁ切り出しておいて問題が起きたら修正すればいいんだし(雑)。

で、あとは例えば「お知らせ」なら、お知らせテーブルに「言語」とかいうカラムをつけておいて

ja: お知らせです。
en: It is news
it：saluti

とかって感じでデータを用意して出力すれば、それでOKな感じ。どっちかってぇと「各言語のコンテンツ」用意するのが面倒だよねぇ、的な。
でもまぁそれはコンテンツ用意する人の問題なので、サイトの骨格であるシステム作成のおいちゃん的にはいったん気にしないｗ

さて割と一番大きな本題「HTMLなどの静的*2なファイルの文字」をどうするか、ですが。
大枠として２種類あって、かつその２種類にはそれぞれ亜種がいくつか存在します、ので、それぞれ、ある程度(もしくは簡単に触りだけ)説明をしていきたいかなぁ、ってのが、本文章の趣旨。

大まかには
・テンプレートを切り替える
・出力文字を切り替える
の２種類。
それぞれ、少しかみ砕いて。

テンプレートを切り替える

いやまぁそのまんまなのですが。
例えば「ログイン」Pageがあるとして、ボタンに「ログイン」とか日本語で書かれると、英語圏の人は多分いろいろと困るです。
……いや日本のサイトで「login」って書いてあっても困らない気がビシバシとするのですが、その辺は置いといて。

てっとり早いのは「英語圏用のテンプレート」と「日本語圏用のテンプレート」とを別々に用意して出力を切り替える、って方法がありまして。
対応言語数が少ない＆ページ数が少ない＆更新頻度が低い(更新そのものが少ない)のであれば、割と手っ取り早い解決策だと思われます。

方法としては
・言語ごとにディレクトリを分ける
・言語ごとに拡張子を分ける
って方法がありまして。

例えば(Smartyチックに)login.tpl、ってテンプレートがあるとしますと。
「ディレクトリで分ける」パターンであれば、
templates/ja/login.tpl
templates/en/login.tpl
って風に入れて。
拡張子で分けるのであれば、(Smarty的に「本当の意味での拡張子」はいじると面倒なんで)
templates/login.ja.tpl
templates/login.en.tpl
って感じにすると、切り分けられます。

上述のような切り替えの作業は、どこか一か所にまとめておくといろいろと楽ですよね。
うちのフレームワーク(MagicWeapon)であれば、viewクラスのmake_template_filename()メソッドを上書きして、って感じかなぁ。
まぁ大体の*3コードであれば、どこかしら「テンプレートのファイル名を取得する」的な一点があると思うので、そこを「キュッ」と絞めると、いけると思います。

出力文字を切り替える

対応言語数が多い＆ページ数が多い＆更新頻度が高いのであれば、「出力文字による切り替え」を想定したほうが楽かもしれません……初手面倒ですが。
端的には
・各言語用の翻訳ファイルを用意して
・プログラムを通して文字列を変換する
となります。

これにも方法がいくつかあって、大まかには
・サーバサイドで自力実装
・クライアントサイドで自力実装
・「gettext」ってのが割とあちこちの言語でライブラリとして存在するので、それを使う
のいずれか、になります。

共通があるのでgettextがよさそうなもんですが…それなりに使い方とかお作法とか癖とかがあるので。
PHPの場合は「インストール」も必要ですしねぇ。
前提条件や癖やそのあたりが「呑み込めそう」なら、gettextを使ってみるとよいんじゃないかなぁ、と思います。
xgettextとか面白いんだけどなぁ……「テンプレートエンジンを使う」前提だと、幾分、ハードルが上がったり諸々が以下略。

自力で作る場合は、おおむね
・辞書のフォーマットを決める
・辞書ファイルを必要言語数だけ作る
・テンプレートに辞書ファイルをぶつけて出力する
といった感じ。

んと……ざっぱに、例。
例えば辞書ファイルを「コード: 翻訳文」とします。
日本語と英語を用意してみませう。

辞書.ja

login_button: ログイン
login_text: こちらからログインしてください。
password_reminder_text: パスワードがわからない場合はこのボタンを押してください。
password_reminder_button: パスワードリマインダ

辞書.en

login_button: login
login_text: Please log in from here.
password_reminder_text: Please press this button if you do not know the password.
password_reminder_button: Password reminder

*4

いろいろと面倒なんでいったんSmarty前提。
まずPHP本体側で、「言語にそった辞書」を渡します。
辞書は、key=コード、value=翻訳文、のhash配列方式で情報があると仮定。

// 辞書の選択
if (日本語圏なら) {
  $辞書配列 = 日本語の辞書;
} else if (英語圏なら) {
  $辞書配列 = 英語の辞書;
} else {
    // 例外でもぶん投げるかねぇ
}

// 辞書をアサイン
$smarty_obj->assign('dic', $辞書配列);

んで、Smartyでは、例えばこんな風に記述します。

{$div.login_text}
<form ...>


<button>{$dic.login_button}</button>
</form>

こんな風にしてテンプレートに「一切出力用の自然言語を書かずに」辞書ファイルに追い出すと、まぁいろいろとできたり出来たりします。ざっくりとは。

結論

がっつりと多言語対応って、割と案件数的にも少ない気がするので。
調べると、案外とネットの情報も少ないんですよねぇ……なので、書いてみた。

なんかほかにも手法ありそうなのですが、まずは「こんなのもあるよ〜」的に。
突っ込みとかあったら突っ込んでくださいませ　＞　諸氏

*1:UTF-7？ なにそれ美味しいの？

*2:政敵って変換するFEPについてどう思う？

*3:まっとうな

*4:英訳への突っ込みはこれを禁止するｗｗｗ

大きなIDをどうやってPHPからMySQLに渡す？：詳細：PHPからcall編

とりあえず、ざっくりと接続してデータをfetchAllでゲトって、「ゲトれる事」をほんのりと確認してみましょう。
基本になるコードは以下の通り。
移行、SQL以降部分だけ適宜すりかえながらやっていきます。

<?php

// DB接続
require_once('config.php');
$dsn = 'mysql:dbname=test;host=localhost;charset=utf8mb4';

// 接続オプションの設定
$opt = array (
    PDO::ATTR_EMULATE_PREPARES => false,
);
// 「複文禁止」が可能なら付け足しておく
if (defined('PDO::MYSQL_ATTR_MULTI_STATEMENTS')) {
    $opt[PDO::MYSQL_ATTR_MULTI_STATEMENTS] = false;
}
// 接続
try {
    $dbh = new PDO($dsn, $user, $pass, $opt);
} catch (PDOException $e) {
    // XXX 本当はもう少し丁寧なエラーページを出力する
    echo 'システムでエラーが起きました';
    exit;
}
//var_dump($dbh);

// プリペアド
$sql = 'SELECT * FROM test;';
$pre = $dbh->prepare($sql);

// バインド
// XXX

// 実行
$r = $pre->execute(); // XXX

// データをまとめて取得
$data = $pre->fetchAll(PDO::FETCH_ASSOC);
var_dump($data);

結果

[gallu@hogehoge48 ~]$ php t.php
array(10) {
[0]=>
array(1) {
["i"]=>
int(9223372036854775806)
}
[1]=>
array(1) {
["i"]=>
int(9223372036854775807)
}
[2]=>
array(1) {
["i"]=>
string(19) "9223372036854775808"
}
[3]=>
array(1) {
["i"]=>
string(19) "9223372036854775809"
}
[4]=>
array(1) {
["i"]=>
string(19) "9223372036854775810"
}
[5]=>
array(1) {
["i"]=>
int(9223372036854775806)
}
[6]=>
array(1) {
["i"]=>
int(9223372036854775807)
}
[7]=>
array(1) {
["i"]=>
string(19) "9223372036854775808"
}
[8]=>
array(1) {
["i"]=>
string(19) "9223372036854775809"
}
[9]=>
array(1) {
["i"]=>
string(19) "9223372036854775810"
}
}

…………ほぉ。
戻り値がintだったりstringだったり。
面倒なんで省略しますが、test_autoテーブルも同じ挙動だったので、おそらく「INT_MAXを超える値の場合、値がぐらつかないように、stringで持ってくる」的なギミックがあるんだろうなぁ、と推測。

さて。
実際の動きとしては、大体
・テーブルからID(とそれ以外のデータの塊)を引っ張ってくる
Pageが存在して、それとは別に、大体IDを引数にして
・IDからテーブルを引っ張ってくる
的な動きをするケースってのは、捨て値で売りさばいても豪邸が立つくらいに大量に頻出する処理かと思われます。
「面倒だし興味あるから、一端、全体をなめたい」ので、foreachを使って、全体をなめてみます。
ちょいと全体が長いので、レコード件数の少ないtest_autoでテストしますが、testテーブルでも同じ結果でした。
まずは、一般的に推奨されていると思われる「暗黙の変換が起きないように、適切にintでキャストしてINT指定する」パターン。

// プリペアド
//$sql = 'SELECT * FROM test;';
$sql = 'SELECT * FROM test_auto;';
$pre = $dbh->prepare($sql);

// バインド
// XXX

// 実行
$r = $pre->execute(); // XXX

// データをまとめて取得
$data = $pre->fetchAll(PDO::FETCH_ASSOC);
//var_dump($data);

//$sql = 'SELECT * FROM test WHERE i = :i;';
$sql = 'SELECT * FROM test_auto WHERE i = :i;';
$pre = $dbh->prepare($sql);
foreach($data as $datum) {
var_dump($datum['i']);
    // バインド
    $pre->bindValue(':i', (int)$datum['i'], PDO::PARAM_INT);

    // 実行
    $r = $pre->execute(); // XXX

    // 取得して出力
    var_dump( $pre->fetchAll(PDO::FETCH_ASSOC) );
}

結果

[gallu@hogehoge48 ~]$ php t.php
int(9223372036854775806)
array(1) {
[0]=>
array(1) {
["i"]=>
int(9223372036854775806)
}
}
int(9223372036854775807)
array(1) {
[0]=>
array(1) {
["i"]=>
int(9223372036854775807)
}
}
string(19) "9223372036854775808"
array(1) {
[0]=>
array(1) {
["i"]=>
int(9223372036854775807)
}
}
string(19) "9223372036854775809"
array(1) {
[0]=>
array(1) {
["i"]=>
int(9223372036854775807)
}
}
string(19) "9223372036854775810"
array(1) {
[0]=>
array(1) {
["i"]=>
int(9223372036854775807)
}
}
string(19) "9223372036854775811"
array(1) {
[0]=>
array(1) {
["i"]=>
int(9223372036854775807)
}
}

ん……わかりやすく「切られてる」感じですねぇ。丸め誤差で丸められちゃってるんだか、INT_MAXで切られてるんだか。
いずれにしても、意図しているレコードではないレコード引っ張ってきちゃってるの感じ。
クエリログ覗いてみませう。

11064 Prepare SELECT * FROM test_auto WHERE i = ?
11064 Close stmt
11064 Execute SELECT * FROM test_auto WHERE i = 9223372036854775806
11064 Execute SELECT * FROM test_auto WHERE i = 9223372036854775807
11064 Execute SELECT * FROM test_auto WHERE i = 9223372036854775807
11064 Execute SELECT * FROM test_auto WHERE i = 9223372036854775807
11064 Execute SELECT * FROM test_auto WHERE i = 9223372036854775807
11064 Execute SELECT * FROM test_auto WHERE i = 9223372036854775807
11064 Close stmt

………あぁあ orz
切られてる切られてる。ドきっちり、切られてる(INT_MAXで切られてるのか丸め誤差で丸められてるのかは不明。アウトな時点で、それ以上の興味がわかなかったのでｗ)。
可能性としては
・指数表記で出力されてデータが取れない
・INT_MAXで切られる
・丸め誤差に従って適切(適当)に丸められる
のあたりを予見していたので、まぁ予想通りっちゃぁ予想どおり。

お次。「文字列による暗黙の変換」を試してみる。
一応丁寧に「第二引数をstringでキャスト」＋「第三引数に明示的にSTRを指定」。

// プリペアド
//$sql = 'SELECT * FROM test;';
$sql = 'SELECT * FROM test_auto;';
$pre = $dbh->prepare($sql);

// バインド
// XXX

// 実行
$r = $pre->execute(); // XXX

// データをまとめて取得
$data = $pre->fetchAll(PDO::FETCH_ASSOC);
//var_dump($data);

//$sql = 'SELECT * FROM test WHERE i = :i;';
$sql = 'SELECT * FROM test_auto WHERE i = :i;';
$pre = $dbh->prepare($sql);
foreach($data as $datum) {
var_dump($datum['i']);
    // バインド
    //$pre->bindValue(':i', (int)$datum['i'], PDO::PARAM_INT);
    $pre->bindValue(':i', (string)$datum['i'], PDO::PARAM_STR);

    // 実行
    $r = $pre->execute(); // XXX

    // 取得して出力
    var_dump( $pre->fetchAll(PDO::FETCH_ASSOC) );
}

結果

[gallu@hogehoge48 ~]$ php t.php
int(9223372036854775806)
array(1) {
[0]=>
array(1) {
["i"]=>
int(9223372036854775806)
}
}
int(9223372036854775807)
array(1) {
[0]=>
array(1) {
["i"]=>
int(9223372036854775807)
}
}
string(19) "9223372036854775808"
array(1) {
[0]=>
array(1) {
["i"]=>
string(19) "9223372036854775808"
}
}
string(19) "9223372036854775809"
array(1) {
[0]=>
array(1) {
["i"]=>
string(19) "9223372036854775809"
}
}
string(19) "9223372036854775810"
array(1) {
[0]=>
array(1) {
["i"]=>
string(19) "9223372036854775810"
}
}
string(19) "9223372036854775811"
array(1) {
[0]=>
array(1) {
["i"]=>
string(19) "9223372036854775811"
}
}

うんこっちだと取れる。

クエリログ確認

11069 Prepare SELECT * FROM test_auto WHERE i = ?
11069 Close stmt
11069 Execute SELECT * FROM test_auto WHERE i = '9223372036854775806'
11069 Execute SELECT * FROM test_auto WHERE i = '9223372036854775807'
11069 Execute SELECT * FROM test_auto WHERE i = '9223372036854775808'
11069 Execute SELECT * FROM test_auto WHERE i = '9223372036854775809'
11069 Execute SELECT * FROM test_auto WHERE i = '9223372036854775810'
11069 Execute SELECT * FROM test_auto WHERE i = '9223372036854775811'
11069 Close stmt

うん、こんな感じだよねぇ………暗黙の変換が気になる気になるｗ

一応、軽く無駄な抵抗を試みてみる。

$sql = 'SELECT * FROM test_auto WHERE i = cast(:i as UNSIGNED);';

ってやって、値を「明示的にキャスト」してみる。してみるだけ。

結果

[gallu@hogehoge48 ~]$ php t.php
int(9223372036854775806)
array(1) {
[0]=>
array(1) {
["i"]=>
int(9223372036854775806)
}
}
int(9223372036854775807)
array(1) {
[0]=>
array(1) {
["i"]=>
int(9223372036854775807)
}
}
string(19) "9223372036854775808"
array(1) {
[0]=>
array(1) {
["i"]=>
string(19) "9223372036854775808"
}
}
string(19) "9223372036854775809"
array(1) {
[0]=>
array(1) {
["i"]=>
string(19) "9223372036854775809"
}
}
string(19) "9223372036854775810"
array(1) {
[0]=>
array(1) {
["i"]=>
string(19) "9223372036854775810"
}
}
string(19) "9223372036854775811"
array(1) {
[0]=>
array(1) {
["i"]=>
string(19) "9223372036854775811"
}
}

クエリログ

11077 Prepare SELECT * FROM test_auto WHERE i = cast(? as UNSIGNED)
11077 Close stmt
11077 Execute SELECT * FROM test_auto WHERE i = cast('9223372036854775806' as UNSIGNED)
11077 Execute SELECT * FROM test_auto WHERE i = cast('9223372036854775807' as UNSIGNED)
11077 Execute SELECT * FROM test_auto WHERE i = cast('9223372036854775808' as UNSIGNED)
11077 Execute SELECT * FROM test_auto WHERE i = cast('9223372036854775809' as UNSIGNED)
11077 Execute SELECT * FROM test_auto WHERE i = cast('9223372036854775810' as UNSIGNED)
11077 Execute SELECT * FROM test_auto WHERE i = cast('9223372036854775811' as UNSIGNED)
11077 Close stmt

あぁよかったここにはプリペアド効くんだ。
効率の良し悪しは不明。ただまぁ「暗黙に変換される」よりは、まだしも「明示的な変換」のほうが、1mmほどはマシなんじゃなかろうかなぁ、っと。

とりあえずここまでで
・INT_MAXを超える値を、intでキャストするとちゃんと動かなくなる
状態が見えてきたので。
そうすると、STRING一択、(暗黙または明示による、SQL側での文字→数値)変換一択、しかとりあえず選択肢ないかなぁ、的な。

で、そうすると後は外部から入ってくるIDの、最低限のvalidate。
これについては、おいちゃんは ctype_digit() 関数がぴったり、だと思う。
注意点がないでもないんだけど。それについては「引数を明示的にstringでキャストすればいいじゃない」って思ってるｗ。そもそも「引数はstring」って明示されてるモノなんだし。
ちゃんとその辺を気にすると、PHP7に行った時に楽よ？ｗ

その辺を踏まえての、DB接続以降の「ID受け取って１件取り出す」側の、サンプルっぽいブツ。

// データを取得したっぽい処理
$id = '9223372036854775810';
//$id = '92233720hoge'; // NGな値

// 超絶ざっくりvalidate
if (false === ctype_digit((string)$id)) {
    echo 'おかしなIDです';
    exit;
}

//$sql = 'SELECT * FROM test WHERE i = :i;';
$sql = 'SELECT * FROM test_auto WHERE i = cast(:i as UNSIGNED);';
$pre = $dbh->prepare($sql);

// バインド
$pre->bindValue(':i', (string)$id, PDO::PARAM_STR);

// 実行
$r = $pre->execute(); // XXX

// 取得して出力
var_dump( $pre->fetchAll(PDO::FETCH_ASSOC) );

とりあえず、こんな感じかなぁ、と。

大きなIDをどうやってPHPからMySQLに渡す？：詳細：MySQL編

とりあえず「思考の流れと調査の流れ」をそのまま書くので、読みにくいのはごめんなさいまし。

まずはいくつか検証テーブルを用意…する前に、MySQL自体の動きの確認を。
直近、参考にさせていただいたのは、hnwさんのこちらのサイト。
http://d.hatena.ne.jp/hnw/20120405

例題にある、以下のSQLを実際に流して、動きの確認をしました。
確認したのは、Server version: 5.6.10。ちょいと古いんだけど、ちょうど年末の整理でつぶす直前のサーバだったので、気兼ねなくｗ

create table decimal_test(id integer auto_increment primary key, a decimal(20));
insert into decimal_test(a) values(9007199254740992),(9007199254740993),(9007199254740994),(9007199254740995),(9007199254740996);
select * from decimal_test where a='9007199254740993';

mysql> select * from decimal_test where a='9007199254740993';
+----+------------------+
｜ id ｜ a ｜
+----+------------------+
｜ 1 ｜ 9007199254740992 ｜
｜ 2 ｜ 9007199254740993 ｜
+----+------------------+
2 rows in set (0.00 sec)

うん、記述通り。

create table bigint_test(id integer auto_increment primary key, a bigint);
insert into bigint_test(a) values(9007199254740992),(9007199254740993),(9007199254740994),(9007199254740995),(9007199254740996);
select * from bigint_test where a='9007199254740993';

mysql> select * from bigint_test where a='9007199254740993';
+----+------------------+
｜ id ｜ a ｜
+----+------------------+
｜ 2 ｜ 9007199254740993 ｜
+----+------------------+
1 row in set (0.00 sec)

こちらも記述通り。

とりあえず、考察の前提が「AUTO_INCREMENTでの値」のお話なんで
・計算はしない
・型はint系(っていうかぶっちゃけるとbigint unsigned一択)
で、以降お話をすすめます。

さて。
MySQLのマニュアルにも、これに近しいお話は書いてありまして。

文字列カラムと数字との比較では、MySQL はカラム上のインデックスを使用して、値をすばやく検索できません。str_col がインデックスの付いた文字列カラムである場合は、次のステートメントで検索を実行するときに、そのインデックスを使用できません。
-略-
その理由は、'1'、' 1'、'1a' のように、値 1 に変換できるさまざまな文字列があるためです。
このような数字は不正確であるため、浮動小数点数 (または浮動小数点数に変換される値) を使用する比較は概算になります。これにより、整合性のない結果が表示される可能性があります。

ってなわけで、書いてある内容を試してみます。

書いてあるのは

mysql> SELECT '18015376320243458' = 18015376320243458;
-> 1
mysql> SELECT '18015376320243459' = 18015376320243459;
-> 0

実験。

mysql> SELECT '18015376320243458' = 18015376320243458;
+-----------------------------------------+
｜ '18015376320243458' = 18015376320243458 ｜
+-----------------------------------------+
｜ 1 ｜
+-----------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT '18015376320243459' = 18015376320243459;
+-----------------------------------------+
｜ '18015376320243459' = 18015376320243459 ｜
+-----------------------------------------+
｜ 1 ｜
+-----------------------------------------+
1 row in set (0.00 sec)

………まてこら。話が違う！！
ただ、実はちょいと別の角度から、怪しい動きをします。
数値をちょいと変えるよ ＆ 大体「カラム＝値」で聞くのと、カラムのほうが数値型なので、順番も入れ替え。

mysql> SELECT 9223372036854775808 = '9223372036854775808';
+---------------------------------------------+
｜ 9223372036854775808 = '9223372036854775808' ｜
+---------------------------------------------+
｜ 1 ｜
+---------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT 9223372036854775808 = '9223372036854775809';
+---------------------------------------------+
｜ 9223372036854775808 = '9223372036854775809' ｜
+---------------------------------------------+
｜ 1 ｜
+---------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT 9223372036854775808 = '9223372036854775810';
+---------------------------------------------+
｜ 9223372036854775808 = '9223372036854775810' ｜
+---------------------------------------------+
｜ 1 ｜
+---------------------------------------------+
1 row in set (0.00 sec)

ほら(悪い方に)あたった。丸め誤差出てる。

ほかのすべてのケースでは、引数は浮動小数点 (実) 数として比較されます。

って記述があるので、大体状況の予想はできる。

…んと

mysql> SELECT '9223372036854775808' + 0;
+---------------------------+
｜ '9223372036854775808' + 0 ｜
+---------------------------+
｜ 9.223372036854776e18 ｜
+---------------------------+
1 row in set (0.08 sec)

mysql> SELECT '9223372036854775809' + 0;
+---------------------------+
｜ '9223372036854775809' + 0 ｜
+---------------------------+
｜ 9.223372036854776e18 ｜
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT '9223372036854775810' + 0;
+---------------------------+
｜ '9223372036854775810' + 0 ｜
+---------------------------+
｜ 9.223372036854776e18 ｜
+---------------------------+
1 row in set (0.00 sec)

こーゆー感じ。
倍精度ったって限界はあるので。このあたりの数値で「１違う」値って、誤差で吸収されて「同じ値」になっちゃったりするのだよねぇ。
………じゃぁ「select * from bigint_test where a='9007199254740993';」がうまくいった、丸め誤差に巻き込まれなかった理由はなにか？

確定できるわけではないのですが、こんな風に組み替えると、こんな風に結果が変わります。

mysql> SELECT 9223372036854775808 = CAST('9223372036854775808' as UNSIGNED);
+---------------------------------------------------------------+
｜ 9223372036854775808 = CAST('9223372036854775808' as UNSIGNED) ｜
+---------------------------------------------------------------+
｜ 1 ｜
+---------------------------------------------------------------+
1 row in set (0.02 sec)

mysql> SELECT 9223372036854775808 = CAST('9223372036854775809' as UNSIGNED);
+---------------------------------------------------------------+
｜ 9223372036854775808 = CAST('9223372036854775809' as UNSIGNED) ｜
+---------------------------------------------------------------+
｜ 0 ｜
+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT 9223372036854775808 = CAST('9223372036854775810' as UNSIGNED);
+---------------------------------------------------------------+
｜ 9223372036854775808 = CAST('9223372036854775810' as UNSIGNED) ｜
+---------------------------------------------------------------+
｜ 0 ｜
+---------------------------------------------------------------+
1 row in set (0.00 sec)

なんとなし「実数(浮動小数点)変換」のケースと「整数変換」のケースがあるんじゃないかなぁ、という予想が、少しばっかり*1。
上述を踏まえて、少し「実際にテーブル作って」挙動の確認をしてみたいかなぁ、と思います。

下準備。INSERT、まぁしないのですが「文字と数値」で、両方の値を入れてみましょう(少し蛇足な実験ｗ)。
あとは、本来的にターゲットであるAUTO_INCREMENT。

DROP TABLE IF EXISTS test;
CREATE TABLE test (
i bigint unsigned
)ENGINE=InnoDB;
INSERT INTO test VALUES (9223372036854775806),(9223372036854775807),(9223372036854775808),(9223372036854775809),(9223372036854775810);
INSERT INTO test VALUES ('9223372036854775806'),('9223372036854775807'),('9223372036854775808'),('9223372036854775809'),('9223372036854775810');

DROP TABLE IF EXISTS test_auto;
CREATE TABLE test_auto (
i bigint unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (i)
)ENGINE=InnoDB auto_increment = 9223372036854775806;
INSERT INTO test_auto VALUES (NULL), (NULL), (NULL), (NULL), (NULL), (NULL);

下確認

mysql> SELECT * FROM test;
+---------------------+
｜ i ｜
+---------------------+
｜ 9223372036854775806 ｜
｜ 9223372036854775807 ｜
｜ 9223372036854775808 ｜
｜ 9223372036854775809 ｜
｜ 9223372036854775810 ｜
｜ 9223372036854775806 ｜
｜ 9223372036854775807 ｜
｜ 9223372036854775808 ｜
｜ 9223372036854775809 ｜
｜ 9223372036854775810 ｜
+---------------------+
10 rows in set (0.00 sec)

すんなりと入ってます。

さて、比較演算子を実験。

mysql> SELECT * FROM test WHERE i = 9223372036854775809;
+---------------------+
｜ i ｜
+---------------------+
｜ 9223372036854775809 ｜
｜ 9223372036854775809 ｜
+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM test WHERE i = '9223372036854775809';
+---------------------+
｜ i ｜
+---------------------+
｜ 9223372036854775809 ｜
｜ 9223372036854775809 ｜
+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM test_auto WHERE i = 9223372036854775809;
+---------------------+
｜ i ｜
+---------------------+
｜ 9223372036854775809 ｜
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test_auto WHERE i = '9223372036854775809';
+---------------------+
｜ i ｜
+---------------------+
｜ 9223372036854775809 ｜
+---------------------+
1 row in set (0.00 sec)

ふむぅ。
単純に「SELECTに比較演算子を与えて出力」ん時は丸め誤差巻き込んでtrue返してたのに、実際のWHERE句になると挙動が違う。
どっちかってぇと「CASTした時」に近いような印象を受ける感じですねぇ。

まぁ、ここについてはhnwさんのほうでも

マッチしたのは1レコードだけでした。このことから、BIGINT型の値と文字列リテラルとを比較した場合は64ビット整数として比較されていることがわかります。
-壮絶に中略-
MySQLの数値型と文字列型の比較は浮動小数点数比較になるようにマニュアルに書いてありますが、整数比較になる例外ケースがいくつか見つかりました。具体的には、BIGINT型と文字列リテラル、インデックスつきのDECIMAL型と文字列リテラル、という2組が整数比較されているとわかりました。

って書かれてますし。
細かい話をすると「WHERE句だけなんかなぁ？」とも思うのですが、まぁ、使うし使いたいのはWHERE句で、なんで、気にしないｗ

また、MySQLのマニュアルのほうにも
https://dev.mysql.com/doc/refman/5.6/ja/numeric-type-overview.html

BIGINT カラムについて注意の必要な点は、次のとおりです。
-中略-
文字列を使用して格納すると、いつでも正確な整数値を BIGINT カラムに格納できます。この場合、MySQL は、中間倍精度表現を含まない文字列から数値に変換します。

とあるので。
上述は「格納」ですが、比較についてもなんとなし「中間倍精度表現を含まない文字列から数値に変換」ってロジックが流れている可能性が想起されるような気が、びしばしとするのでございます。

このあたりを踏まえたうえで、じゃぁ次は「PHPからcallするとどうなるの？」っていう、疑問のメインをあさってみたいと思います。

*1:余談ですが。CASTで「実数への変換」って、ないんですかねぇ？ いや「ちゃんとマニュアル読み切れてないだけ」な気もするのですが