がるの健忘録

エンジニアでゲーマーで講師で占い師なおいちゃんのブログです。

トップ > MySQL

大きなIDをどうやってPHPからMySQLに渡す？：発端とまとめ

PHP MySQL

あるタイミングで、プリペアドステートメント回りのお話と、IDのカラム(によって決まる最大値)のお話と、intサイズのお話が別々に来た時に……頭ん中で混ざって、「あれ？」と思った事があったので調べてみた、って感じになります。


まず話に出ていたのが「AUTO_INCREMENTのカラムの型」のお話。
もうちょっと突っ込むと「intだと足りなくなる瞬間が以下略」。2147483647、ようは21億ちょい、なので、動かし方によっては十分に手が届いちゃう範囲。
int unsignedにしても4294967295だし、そもそもAUTO_INCREMENTなんで負の値いらないからまぁ「bigint unsignedだよねぇ」ってお話があって、これが素材その１。


bitintは8バイトなので、unsignedだと18446744073709551615とかいう、クソ怪しい値までOK……なんだけど。
PHPって、64bit環境の場合、あの子「signed intまでしか整数扱えない(以降は浮動小数点で扱う)」なので………あれ？ 9223372036854775807 まではいいんだが、9223372036854775808以降は？
これが素材２。


PDOのプリペアドステートメントは「第二引数の変数の型と第三引数の定数の両方がINT」以外はstring扱いだよねぇ、って話が、全然別の流れで普通に出てきて。
………あれ？ 「PHP的にintで扱えない値はどうなるの？」って疑問がむくむくと湧いてきて。
これが素材３。


というわけで、素材１〜３までを合成………するまえに。
とりあえず、最低限のチェックから。

<?php

// PHP_INT_MAXの「次の数」の確認
$i = PHP_INT_MAX;
$i += 1;
echo "---\n";
var_dump($i);
printf("%f\n", $i);
echo "---\n";
var_dump((int)$i);
var_dump(intval($i));
echo "---\n";
printf("%d\n", (int)$i);
printf("%d\n", intval($i));
echo "---\n";


// 少し雑に「大きな数」の確認(INT_MAXの先頭に1追加した数値)
$i = 19223372036854775807;
echo "---\n";
var_dump($i);
printf("%f\n", $i);
echo "---\n";
var_dump((int)$i);
var_dump(intval($i));
echo "---\n";
printf("%d\n", (int)$i);
printf("%d\n", intval($i));
echo "---\n";

---
float(9.2233720368548E+18)
9223372036854775808.000000
---
int(-9223372036854775808)
int(-9223372036854775808)
---
-9223372036854775808
-9223372036854775808
---
---
float(1.9223372036855E+19)
19223372036854775808.000000
---
int(776627963145224192)
int(776627963145224192)
---
776627963145224192
776627963145224192
---

うわぁい(苦笑
intでキャストもintval関数も、「負の値に行ったり(これはまぁわかる)」、よくわからん数値になってきたり(多分単純にビットあふれが切り捨てられてる)。
まぁ、マニュアルにも書いてあるしなぁ


http://php.net/manual/ja/function.intval.php

最大値はシステムに依存します。32 ビットシステムでは、 最大の符号付き整数の範囲 -2147483648 〜 2147483647 となります。 このため、そのようなシステムでは intval('1000000000000') は 2147483647 を返します。 64 ビットシステムにおける最大の符号付き整数は 9223372036854775807 となります。


なので、大きなIDを
・受け取って
・INTでキャストして
・プレースホルダにバインド
すると、多分間違いなくなんとなくまずもっておそらく「NGであろうなぁ」と思われるに至り、これは「実験せにゃなぁ」と思ったわけでございます。


で…細かい実験は長いんで、後で書きますが。
ものすごく端的に要約すると、現時点のおいちゃん見解としては、大体以下の通りかなぁ、と。
・(IDなんで演算とかしない前提で)文字列で受け取り、文字列で渡すようにする
・WHERE句のIDに「明示的にCASTするかどうか」は、お好みで。可能性として「CASTしておいたほうが効率が良い、かも、しれない」のと「何となくせめて明示したい」ｗ
・IDはvalidateする。ctype_digit()関数がよいと思う
・「文字列から数値への、WHERE句での使用時の暗黙の(またはCASTによる)変換」の挙動が変わらないように、祈るｗ


いやまぁ「例えば、bigint unsignedを避ける(bigint signedにする)」＋「PHPは64bit環境」って選択肢もないわけではないのですがｗ
まぁ少ないとは思うのですが、サービスの余命考えた時に「どっちかねぇ？」って感じになると思うのです。


いやまぁ実際、現時点のおいちゃん見解も「ど〜かねぇ？」とは思うのですが。暗黙の変換の、しかも「明記されているわけではない」挙動に頼る、ってのも(CAST使えば"暗黙"ではないですが一応)。
ただ、現状、それ以外に今一つ「よいアイデア」が浮かばないんですよねぇ……というわけで、現状における「苦肉の策」だと思っていただけると。と。
なので。「普通の整数を扱う」ときはちゃんとINTで扱って、ただ「INT_MAXを超える可能性があり」かつ「算術演算が発生しない」AUTO_INCREMENTなIDについて」のみ、例外的に、上述のような方法を取らざるを得ないのかなぁ、という。


もうちょっと妙手があればなぁ、と思うので、コメントなどありましたら、お気軽によろしくお願いいたします！！

異字体？

MySQL その他技術

大本ネタはこちら。
http://moriyoshi.hatenablog.com/entry/2017/03/13/011005
ここで「異体字セレクタ」ってのをはじめて知って、ちょろっと調べものをしたので、備忘録的に。
UnicodeっつかUTF-8で「１文字づつ」に分割するごにょごにょを書きたいかもなぁ、とか、ちょろっと妄想をしていたので。


とりあえず。

(二点しんにょう U+8FBB + VS18) ⇔「辻??」(一点しんにょう U+8FBB + VS17)

……ふお？
U+8FBB はわかるんだが、 + VS18、って、なにかしらん？
が、startライン。


http://itpro.nikkeibp.co.jp/article/COLUMN/20110124/356398/?rt=nocnt
見ると、「E9 82 8A F3 A0 84 80」とか「E9 82 8A F3 A0 84 81」とか。


念の為に、UTF-8のエンコーディング方法を確認。
https://ja.wikipedia.org/wiki/UTF-8
………ぶも？
5バイト長とか6バイト長とかある？？？
一端より道して調べもの。…いやもしあるんなら。MySQLのutf8mb4が、些か、怖いやもしれぬので。


https://ja.wikipedia.org/wiki/Unicode%E4%B8%80%E8%A6%A7%E8%A1%A8
http://www.unicode.org/roadmaps/


ふむ。第16面でも「U+F0000 〜 U+10FFFF」なのねん。
これだと十分に４バイトで入る。…5バイト長とか6バイト長は「将来に向けての布石」なのかしらん？
とりあえず「当面の厄介」は消えたぽいので、「5バイト長とか6バイト長」の調査は一端ここで打ち切り。


もどして…とりあえず、ビット列に分解して、有効な値を取り出してみる。


E9 82 8A F3 A0 84 80
1110 1001 1000 0010 1000 1010 1111 0011 1010 0000 1000 0100 1000 0000


E9 82 8A F3 A0 84 81
1110 1001 1000 0010 1000 1010 1111 0011 1010 0000 1000 0100 1000 0001


使う子だけ、はじき出す…とりあえず先頭は多分３バイトのはず。
1110 yyyy 10yx xxxx 10xx xxxx
1110 1001 1000 0010 1000 1010 1111 0011 1010 0000 1000 0100 1000 0000
1110 1001 1000 0010 1000 1010 1111 0011 1010 0000 1000 0100 1000 0001


どっちも
1001000010001010
908A


うん確かに「邊」の字だ。
てことは手前３バイトは文字なんだ…後ろの４バイトを解析してみませう。
先頭が11110なんで、UTF-8的には「４バイト文字」のはず。


1111 0yyy 10yy xxxx 10xx xxxx 10xx xxxx
1111 0011 1010 0000 1000 0100 1000 0000
1111 0011 1010 0000 1000 0100 1000 0001


011100000000100000000
011100000000100000001


E0100
E0101


一覧を見てみる…
https://ja.wikipedia.org/wiki/Unicode%E4%B8%80%E8%A6%A7_E0000-E0FFF
「VS17」とか「VS18」とか。


あぁ！！
単純に、ここらへん(おそらく、E0100〜E01E0F)までは「異字体(IVS：Ideographic Variation Sequence)だよ！！」って取決めなのか！！
うん、腑に落ちた。


ってことは「１文字を切り出す」時に、もし後ろにこの子がいたら「前の字とセットで」持っておいてあげる必要があるんだなぁ。
PHPの実装とかどうなってるんだろ？
まぁその辺はまた後日、気になったら調べてみませうｗ

MySQLのmemcachedインターフェース(InnoDB Integration with memcached)を実験してみた：備忘録

MySQL

きちんと把握したい人以外は特に読まなくてもイイ内容ｗ
ただまぁ「きちんとした把握もせずに使う人」が技術者なのか？ という問いが、おいちゃん的にはあったりするんだけど、まぁ人それぞれなので。

containersテーブルについて少し

基本的は、前述の通り「(db_schema)にある(db_table)ってテーブルの、(key_columns)をkeyに、(value_columns)をvalueに持ってる」程度の感じ。
あとはflags、cas_column、expire_time_columnは「カラム名の指定」。この３つのカラムが、(db_table)テーブルん中に必要みたい。
とりあえずおいちゃんは

  flags int,
  cas_column bigint UNSIGNED,
  expire_time_column int,

ってやってた。最適解があるかもしれないので、とりあえずこれは「動いたよ〜」程度の、無考察なブツなんでご注意のほどを。


flgasのカラムには、とりあえず0をぶちこんでおくと「未使用」って宣言になるみたい。
https://dev.mysql.com/doc/refman/5.6/en/innodb-memcached-internals.html

flags specifies the columns used as flags (a user-defined numeric value that is stored and retrieved along with the main value) for memcached. It is also used as the column specifier for some operations (such as incr, prepend) if memcached value is mapped to multiple columns. So the operation would be done on the specified column. For example, if you have mapped a value to 3 columns, and only want the increment operation performed on one of these columns, you can use flags to specify which column will be used for these operations. If you do not use the flags column, set its value to 0 to indicate that it is unused.


cas_columnとexpire_time_columnも、一端、０でいいみたい。
https://dev.mysql.com/doc/refman/5.6/en/innodb-memcached-internals.html

cas_column and expiration_time_column are used specifically to store the cas (compare-and-swap) and exp (expiry) value of memcached. Those values are related to the way memcached hashes requests to different servers and caches data in memory. Because the InnoDB memcached plugin is so tightly integrated with a single memcached daemon, and the in-memory caching mechanism is handled by MySQL and the buffer pool, these columns are rarely needed in this type of deployment. If you do not use these columns, set their value to 0 to indicate that the columns are unused.


unique_idx_name_on_keyは…
https://dev.mysql.com/doc/refman/5.6/en/innodb-memcached-internals.html

unique_idx_name_on_key is the name of the index on the key column. It must be a unique index. It can be the primary key or a secondary index. Preferably, make the key column the primary key of the InnoDB table. Doing so saves a lookup step over using a secondary index for this column. You cannot make a covering index for memcached lookups; InnoDB returns an error if you try to define a composite secondary index over both the key and value columns.

ってあるんだけど。実際には'PRIMARY'っていう固定の文字列でいけた。'PRIMARY KEY'でもいけた。
ので…使ってるのかどうか今ひとつ不明。

テストの列挙

先にお断り。
長いです。大量です。長いです。

drop table test1;
create table test1 (
  `key` varbinary(256),
  `val` varbinary(256),
  `flags` int,
  cas_column bigint UNSIGNED,
  expire_time_column int,
  PRIMARY KEY(`key`)
);
insert into innodb_memcache.containers set name='test1', db_schema='furu_test', db_table='test1', key_columns='key', value_columns='val', flags='flags', cas_column='cas_column', expire_time_column='expire_time_column', unique_idx_name_on_key='PRIMARY';
insert into test1 set `key`='d1', val='val', flags=0, cas_column=0, expire_time_column=0;
<?php
// 接続
$m = new Memcache();
$r = $m->addServer('localhost', 11211);
// データの取得
$v = $m->get('@@test1.d1');
var_dump($v);

…うまくいかない。
なんで？(答え：keyがvarbinaryだからなんだが)

create table test2 (
  pkeys varbinary(256) not null,
  val varbinary(256),
  flags int,
  cas_column bigint UNSIGNED,
  expire_time_column int,
  PRIMARY KEY(pkeys)
);
insert into test2 set pkeys='d1', val='val', flags=0, cas_column=0, expire_time_column=0;
insert into innodb_memcache.containers set name='test2', db_schema='furu_test', db_table='test2', key_columns='pkeys', value_columns='val', flags='flags', cas_column='cas_column', expire_time_column='expire_time_column', unique_idx_name_on_key='PRIMARY';
insert into innodb_memcache.containers set name='test2t', db_schema='test', db_table='test2', key_columns='pkeys', value_columns='val', flags='flags', cas_column='cas_column', expire_time_column='expire_time_column', unique_idx_name_on_key='PRIMARY';

とりあえず、keyをnot nullにしてみる。
あと、念のため「database名"test"の中」にテーブルを設置してみる(大抵のサンプルがそこにある前提で書いてあったから念のため)。
………駄目。
なんだろう？

create table test3 (
  pkeys varchar(256) not null,
  val varchar(256),
  flags int,
  cas_column bigint UNSIGNED,
  expire_time_column int,
  PRIMARY KEY(pkeys)
);
insert into test3 set pkeys='d1', val='val', flags=0, cas_column=0, expire_time_column=0;
insert into innodb_memcache.containers set name='test3t', db_schema='test', db_table='test3', key_columns='pkeys', value_columns='val', flags='flags', cas_column='cas_column', expire_time_column='expire_time_column', unique_idx_name_on_key='PRIMARY';
insert into innodb_memcache.containers set name='test3', db_schema='furu_test', db_table='test3', key_columns='pkeys', value_columns='val', flags='flags', cas_column='cas_column', expire_time_column='expire_time_column', unique_idx_name_on_key='PRIMARY';

あ、通った。
なんだ？

create table test4 (
  pkeys varchar(256) not null,
  val varbinary(256),
  flags int,
  cas_column bigint UNSIGNED,
  expire_time_column int,
  PRIMARY KEY(pkeys)
);
insert into test4 set pkeys='d1', val='val', flags=0, cas_column=0, expire_time_column=0;
insert into innodb_memcache.containers set name='test4', db_schema='furu_test', db_table='test4', key_columns='pkeys', value_columns='val', flags='flags', cas_column='cas_column', expire_time_column='expire_time_column', unique_idx_name_on_key='PRIMARY';

もういっちょ同じのを。
…これも通る。
varbinaryイヤなのかしら？
…ってのとは別に、嫌な予感がするのでテスト。

<?php
// 接続
$m = new Memcache();
$r = $m->addServer('localhost', 11211);
// データの取得
$v = $m->get('@@test4.D1');
var_dump($v);

………あぁ通るねぇ orz
varcharだから仕方が無いか orz

create table test5 (
  pkeys char(255) not null,
  val varbinary(256),
  flags int,
  cas_column bigint UNSIGNED,
  expire_time_column int,
  PRIMARY KEY(pkeys)
);
insert into test5 set pkeys='d1', val='val', flags=0, cas_column=0, expire_time_column=0;
insert into innodb_memcache.containers set name='test5', db_schema='furu_test', db_table='test5', key_columns='pkeys', value_columns='val', flags='flags', cas_column='cas_column', expire_time_column='expire_time_column', unique_idx_name_on_key='PRIMARY';

charも通るんだ。

create table test6 (
  pkeys varchar(256) not null,
  val text,
  flags int,
  cas_column bigint UNSIGNED,
  expire_time_column int,
  PRIMARY KEY(pkeys)
);
insert into test6 set pkeys='d1', val='val', flags=0, cas_column=0, expire_time_column=0;
insert into innodb_memcache.containers set name='test6', db_schema='furu_test', db_table='test6', key_columns='pkeys', value_columns='val', flags='flags', cas_column='cas_column', expire_time_column='expire_time_column', unique_idx_name_on_key='PRIMARY';

valにtextも通るねぇ。

create table test7 (
  pkeys varchar(256) not null,
  val blob,
  flags int,
  cas_column bigint UNSIGNED,
  expire_time_column int,
  PRIMARY KEY(pkeys)
);
insert into test7 set pkeys='d1', val='val', flags=0, cas_column=0, expire_time_column=0;
insert into test7 set pkeys='d2', val='0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789', flags=0, cas_column=0, expire_time_column=0;
insert into innodb_memcache.containers set name='test7', db_schema='furu_test', db_table='test7', key_columns='pkeys', value_columns='val', flags='flags', cas_column='cas_column', expire_time_column='expire_time_column', unique_idx_name_on_key='PRIMARY';

…あれ？ valがblobでも通るよ？

create table test8 (
  pkeys varbinary(256) not null,
  val blob,
  flags int,
  cas_column bigint UNSIGNED,
  expire_time_column int,
  PRIMARY KEY(pkeys)
);
insert into test8 set pkeys='d1', val='val', flags=0, cas_column=0, expire_time_column=0;
insert into innodb_memcache.containers set name='test8', db_schema='furu_test', db_table='test8', key_columns='pkeys', value_columns='val', flags='flags', cas_column='cas_column', expire_time_column='expire_time_column', unique_idx_name_on_key='PRIMARY';

keyをvarbinaryにしてみる…うんこっちはfalseか orz
どうも、valueにvarbinaryはよいのだけど、keyにするとむずがるみたいだ orz

drop table test9;
create table test9 (
  pkeys varchar(256) not null,
  val int,
  flags int,
  cas_column bigint UNSIGNED,
  expire_time_column int,
  PRIMARY KEY(pkeys)
);
insert into test9 set pkeys='d1', val=100, flags=0, cas_column=0, expire_time_column=0;
insert into test9 set pkeys='d1 2', val=100, flags=0, cas_column=0, expire_time_column=0;
insert into test9 set pkeys='d1_2', val=101, flags=0, cas_column=0, expire_time_column=0;
insert into innodb_memcache.containers set name='test9', db_schema='furu_test', db_table='test9', key_columns='pkeys', value_columns='val', flags='flags', cas_column='cas_column', expire_time_column='expire_time_column', unique_idx_name_on_key='PRIMARY';

２つ実験。
１つめは「valueにintは？」
答え：通る。ただ、getするとstringになってはくるんだが(苦笑
２つめは「keyの中にあるスペースの扱い」。アンダースコアになってしまうために

$v = $m->get('@@test9.d1 2');
var_dump($v);

って指定をしても、戻りは「string(3) "101"」になる。

create table test10 (
  pkeys int not null,
  val int,
  flags int,
  cas_column bigint UNSIGNED,
  expire_time_column int,
  PRIMARY KEY(pkeys)
);
insert into test10 set pkeys=1, val=100, flags=0, cas_column=0, expire_time_column=0;
insert into innodb_memcache.containers set name='test10', db_schema='furu_test', db_table='test10', key_columns='pkeys', value_columns='val', flags='flags', cas_column='cas_column', expire_time_column='expire_time_column', unique_idx_name_on_key='PRIMARY';

keyにint指定したらどうなるんだろう？
答え：無問題。通りました普通に。

create table test11 (
  pkeys int not null,
  val1 int,
  val2 float,
  val3 varchar(256),
  val4 varbinary(256),
  val5 blob,
  val6 datetime,
  flags int,
  cas_column bigint UNSIGNED,
  expire_time_column int,
  PRIMARY KEY(pkeys)
);
insert into test11 set pkeys=1, val1=100, val2=1.123, val3='varchar', val4='binary', val5='blob', val6='2014-1-1 10:22:33', flags=0, cas_column=0, expire_time_column=0;
insert into innodb_memcache.containers set name='test11', db_schema='furu_test', db_table='test11', key_columns='pkeys', value_columns='val1,val2,val3,val4,val5,val6', flags='flags', cas_column='cas_column', expire_time_column='expire_time_column', unique_idx_name_on_key='PRIMARY';
insert into innodb_memcache.containers set name='test11_1', db_schema='furu_test', db_table='test11', key_columns='pkeys', value_columns='val1', flags='flags', cas_column='cas_column', expire_time_column='expire_time_column', unique_idx_name_on_key='PRIMARY';
insert into innodb_memcache.containers set name='test11_2', db_schema='furu_test', db_table='test11', key_columns='pkeys', value_columns='val1,val2', flags='flags', cas_column='cas_column', expire_time_column='expire_time_column', unique_idx_name_on_key='PRIMARY';
insert into innodb_memcache.containers set name='test11_3', db_schema='furu_test', db_table='test11', key_columns='pkeys', value_columns='val1|val2', flags='flags', cas_column='cas_column', expire_time_column='expire_time_column', unique_idx_name_on_key='PRIMARY';

さて…複数カラムの実験。
ん…「name='test11_1'」以外は全部NG。なんだろ？

create table test12 (
  pkeys varchar(256) not null,
  val1 varchar(256),
  val2 varbinary(256),
  val3 blob,
  flags int,
  cas_column bigint UNSIGNED,
  expire_time_column int,
  PRIMARY KEY(pkeys)
);
insert into test12 set pkeys='d1', val1='varchar', val2='binary', val3='blob', flags=0, cas_column=0, expire_time_column=0;
insert into innodb_memcache.containers set name='test12', db_schema='furu_test', db_table='test12', key_columns='pkeys', value_columns='val1', flags='flags', cas_column='cas_column', expire_time_column='expire_time_column', unique_idx_name_on_key='PRIMARY';
insert into innodb_memcache.containers set name='test12_1', db_schema='furu_test', db_table='test12', key_columns='pkeys', value_columns='val1,val2,val3', flags='flags', cas_column='cas_column', expire_time_column='expire_time_column', unique_idx_name_on_key='PRIMARY';
insert into innodb_memcache.containers set name='test12_2', db_schema='furu_test', db_table='test12', key_columns='pkeys', value_columns='val1|val2|val3', flags='flags', cas_column='cas_column', expire_time_column='expire_time_column', unique_idx_name_on_key='PRIMARY';

「name='test12'」だけ…複数カラムって無理なのかしらん？

drop table test13;
create table test13 (
  pkeys varchar(128) not null,
  val1 varchar(256),
  val2 varchar(256),
  val3 varchar(128),
  flags int,
  cas_column bigint UNSIGNED,
  expire_time_column int,
  PRIMARY KEY(pkeys)
) CHARACTER SET 'utf8mb4', ENGINE=InnoDB;
insert into test13 set pkeys='d1', val1='varchar', val2='binary', val3='blob', flags=0, cas_column=0, expire_time_column=0;
insert into innodb_memcache.containers set name='test13', db_schema='furu_test', db_table='test13', key_columns='pkeys', value_columns='val1', flags='flags', cas_column='cas_column', expire_time_column='expire_time_column', unique_idx_name_on_key='PRIMARY';
insert into innodb_memcache.containers set name='test13_1', db_schema='furu_test', db_table='test13', key_columns='pkeys', value_columns='val1,val2,val3', flags='flags', cas_column='cas_column', expire_time_column='expire_time_column', unique_idx_name_on_key='PRIMARY';
insert into innodb_memcache.containers set name='test13_2', db_schema='furu_test', db_table='test13', key_columns='pkeys', value_columns='val1|val2|val3', flags='flags', cas_column='cas_column', expire_time_column='expire_time_column', unique_idx_name_on_key='PRIMARY';

あ、のった。…varbinaryが絡むと駄目なの？ エライこと冷遇されてないかい？
「value_columns='val1,val2,val3'」と「value_columns='val1|val2|val3'」、とりあえず、どっちの書式でもOKなんだなぁ。
ちなみに値は「string(19) "varchar|binary|blob"」と一本につながってきます。
ついでに

//
$m->set('@@test13_1.d2', '1|2|3');
//
$v = $m->get('@@test13_1.d2');
var_dump($v);

からの

mysql> select * from test13 \G

                                                      • 1. row ---------------------------

pkeys: d1
val1: varchar
val2: binary
val3: blob
flags: 0
cas_column: 0
expire_time_column: 0

                                                      • 2. row ---------------------------

pkeys: d2
val1: 1
val2: 2
val3: 3
flags: 0
cas_column: 20022
expire_time_column: 0
2 rows in set (0.00 sec)

ってな感じで、ちゃんと「分割しての設定」も出来るようです。

create table test14 (
  pkeys varchar(256) not null,
  val1 varbinary(256),
  val2 varbinary(256),
  flags int,
  cas_column bigint UNSIGNED,
  expire_time_column int,
  PRIMARY KEY(pkeys)
);
insert into test14 set pkeys='d1', val1='varchar', val2='binary', flags=0, cas_column=0, expire_time_column=0;
insert into innodb_memcache.containers set name='test14', db_schema='furu_test', db_table='test14', key_columns='pkeys', value_columns='val1,val2', flags='flags', cas_column='cas_column', expire_time_column='expire_time_column', unique_idx_name_on_key='PRIMARY';

うんvarbinaryはやっぱり駄目なんだ。

create table test15 (
  pkeys int not null,
  val1 varchar(256),
  val2 varchar(256),
  val3 varchar(128),
  flags int,
  cas_column bigint UNSIGNED,
  expire_time_column int,
  PRIMARY KEY(pkeys)
) CHARACTER SET 'utf8mb4', ENGINE=InnoDB;
insert into test15 set pkeys=1, val1='varchar', val2='binary', val3='blob', flags=0, cas_column=0, expire_time_column=0;
insert into innodb_memcache.containers set name='test15', db_schema='furu_test', db_table='test15', key_columns='pkeys', value_columns='val1,val2,val3', flags='flags', cas_column='cas_column', expire_time_column='expire_time_column', unique_idx_name_on_key='PRIMARY';

「keyがint」かつ「valueが複数カラム」…ふむ、普通に通る、っと。

create table test17 (
  pkeys varchar(256) not null,
  val1 varchar(256),
  val2 varchar(256),
  val3 varchar(128),
  flags int,
  cas_column bigint UNSIGNED,
  expire_time_column int,
  PRIMARY KEY(pkeys)
) CHARACTER SET 'latin1', ENGINE=InnoDB;
insert into test17 set pkeys=1, val1='varchar', val2='binary', val3='blob', flags=0, cas_column=0, expire_time_column=0;
insert into innodb_memcache.containers set name='test17', db_schema='furu_test', db_table='test17', key_columns='pkeys', value_columns='val1,val2,val3', flags='flags', cas_column='cas_column', expire_time_column='expire_time_column', unique_idx_name_on_key='PRIMARY';

「CHARACTER SET 'latin1'」は通る…latin1はいいんだ。

create table test18 (
  pkeys varchar(256) not null,
  val1 varchar(256),
  val2 varchar(256),
  val3 varchar(128),
  flags int,
  cas_column bigint UNSIGNED,
  expire_time_column int,
  PRIMARY KEY(pkeys)
) CHARACTER SET 'binary', ENGINE=InnoDB;
insert into test18 set pkeys=1, val1='varchar', val2='binary', val3='blob', flags=0, cas_column=0, expire_time_column=0;
insert into innodb_memcache.containers set name='test18', db_schema='furu_test', db_table='test18', key_columns='pkeys', value_columns='val1,val2,val3', flags='flags', cas_column='cas_column', expire_time_column='expire_time_column', unique_idx_name_on_key='PRIMARY';

「CHARACTER SET 'binary'」でbinaryにしてみるとやっぱり嫌がられる。
binaryは一通り、おいやですかしらん？

drop table test19 ;
create table test19 (
  pkeys varchar(256) not null,
  val1 int,
  val2 int,
  val3 int,
  flags int,
  cas_column bigint UNSIGNED,
  expire_time_column int,
  PRIMARY KEY(pkeys)
) CHARACTER SET 'latin1', ENGINE=InnoDB;
insert into test19 set pkeys='d1', val1=10, val2=20, val3=30, flags=0, cas_column=0, expire_time_column=0;
insert into innodb_memcache.containers set name='test19', db_schema='furu_test', db_table='test19', key_columns='pkeys', value_columns='val1,val2,val3', flags='flags', cas_column='cas_column', expire_time_column='expire_time_column', unique_idx_name_on_key='PRIMARY';

valueのintは特に気にしない…っと。
まぁ「string(8) "10|20|30"」ってreturnになるにはしても。

create table test20 (
  pkeys varchar(256) not null,
  val1 text,
  val2 text,
  val3 text,
  flags int,
  cas_column bigint UNSIGNED,
  expire_time_column int,
  PRIMARY KEY(pkeys)
) CHARACTER SET 'latin1', ENGINE=InnoDB;
insert into test20 set pkeys='d1', val1='varchar', val2='binary', val3='blob', flags=0, cas_column=0, expire_time_column=0;
insert into innodb_memcache.containers set name='test20', db_schema='furu_test', db_table='test20', key_columns='pkeys', value_columns='val1,val2,val3', flags='flags', cas_column='cas_column', expire_time_column='expire_time_column', unique_idx_name_on_key='PRIMARY';

まぁこれは乗るねぇ。
…あんまり長い文字列だとどうなるんだろう？ という疑問があるにはしても。

create table test21 (
  pkeys varchar(256) not null,
  val1 blob,
  val2 blob,
  val3 blob,
  flags int,
  cas_column bigint UNSIGNED,
  expire_time_column int,
  PRIMARY KEY(pkeys)
) CHARACTER SET 'latin1', ENGINE=InnoDB;
insert into test21 set pkeys='d1', val1='varchar', val2='binary', val3='blob', flags=0, cas_column=0, expire_time_column=0;
insert into innodb_memcache.containers set name='test21', db_schema='furu_test', db_table='test21', key_columns='pkeys', value_columns='val1,val2,val3', flags='flags', cas_column='cas_column', expire_time_column='expire_time_column', unique_idx_name_on_key='PRIMARY';

…あ、乗った。varbinaryは駄目なのに、blobはいいんだ(苦笑

create table test22 (
  pkeys varchar(256) not null,
  val1 varbinary(128),
  val2 blob,
  val3 blob,
  flags int,
  cas_column bigint UNSIGNED,
  expire_time_column int,
  PRIMARY KEY(pkeys)
) CHARACTER SET 'latin1', ENGINE=InnoDB;
insert into test22 set pkeys='d1', val1='varchar', val2='binary', val3='blob', flags=0, cas_column=0, expire_time_column=0;
insert into innodb_memcache.containers set name='test22', db_schema='furu_test', db_table='test22', key_columns='pkeys', value_columns='val1,val2,val3', flags='flags', cas_column='cas_column', expire_time_column='expire_time_column', unique_idx_name_on_key='PRIMARY';

………blogが乗ったから…と思ったけど、やっぱり駄目なんだよねぇvarbinaryは orz

create table test23 (
  pkeys varchar(256) not null,
  val1 date,
  val2 datetime,
  val3 timestamp,
  flags int,
  cas_column bigint UNSIGNED,
  expire_time_column int,
  PRIMARY KEY(pkeys)
) CHARACTER SET 'latin1', ENGINE=InnoDB;
insert into test23 set pkeys='d1', val1='1970-11-25', val2='2014-1-1 11:22:33', val3='2034-1-1 22:33:44', flags=0, cas_column=0, expire_time_column=0;
insert into innodb_memcache.containers set name='test23', db_schema='furu_test', db_table='test23', key_columns='pkeys', value_columns='val1,val2,val3', flags='flags', cas_column='cas_column', expire_time_column='expire_time_column', unique_idx_name_on_key='PRIMARY';
insert into innodb_memcache.containers set name='test23_1', db_schema='furu_test', db_table='test23', key_columns='pkeys', value_columns='val2', flags='flags', cas_column='cas_column', expire_time_column='expire_time_column', unique_idx_name_on_key='PRIMARY';

「name='test23'」はfalse。
「name='test23_1'」は一応戻ってくるんだけど、値がガッツリと文字化けしてる。まぁ「使うな」って事だわなきっと。

create table test24 (
  pkeys varchar(256) not null,
  val1 blob,
  val2 varchar(128),
  val3 int,
  flags int,
  cas_column bigint UNSIGNED,
  expire_time_column int,
  PRIMARY KEY(pkeys)
) CHARACTER SET 'latin1', ENGINE=InnoDB;
insert into test24 set pkeys='d1', val1='aaa', val2='bbb', val3=999, flags=0, cas_column=0, expire_time_column=0;
insert into innodb_memcache.containers set name='test24', db_schema='furu_test', db_table='test24', key_columns='pkeys', value_columns='val1,val2,val3', flags='flags', cas_column='cas_column', expire_time_column='expire_time_column', unique_idx_name_on_key='PRIMARY';

混ぜると…「string(11) "aaa|bbb|999"」あ、平気だった。


以上、ものっそ長い備忘録ｗ

MySQLのmemcachedインターフェース(InnoDB Integration with memcached)を実験してみた：チューニング周りの備忘録

MySQL

あんまりちゃんと調査してないんで、本気で備忘録程度。


とりあえず、memslapで簡単にベンチマーク確認しておきませう。

# 10,000回のテスト
memslap --servers=localhost --test set
memslap --servers=localhost --test get


以下のパラメタがチューニングと関連あるぽいのですが、全く以て未検証です。
後日、もし気が向いたらなにか記事書き足しますｗ

daemon_memcached_w_batch_size
daemon_memcached_r_batch_size
innodb_api_trx_level
innodb_api_disable_rowlock
innodb_api_enable_mdl
SELECT @@tx_isolation;

MySQLのmemcachedインターフェース(InnoDB Integration with memcached)を実験してみた：準備変

MySQL

とりあえず、以下を先にやっておきましょう。

・必要なconfigの設定(SQL文が流れる)

cat ./plugin/innodb_memcached/innodb_memcache/scripts/innodb_memcached_config.sql | mysql -u root


・プラグインの設定(SQL コンソールにて)

install plugin daemon_memcached soname "libmemcached.so";

mysql.plugin に情報が入る(から、以降、restartしても問題ない)


・memcachedは認証が存在しないため、とりあえずiptablesでブロック(本番は、ロードバランサあたりでフィルタリング、が適切)
記述は、iptablesへの設定内容。

-A INPUT -p tcp -m state --state NEW -m tcp --dport 11211 -j DROP


・PHPでmemcacheまたはmemecachedが使えるように設定
php.iniとかで適宜。
おいちゃんはこんな手順でやりました。適当に妄想補完してください。

wget https://launchpad.net/libmemcached/1.0/1.0.18/+download/libmemcached-1.0.18.tar.gz
tar zvxf libmemcached-1.0.18.tar.gz
cd libmemcached-1.0.18/
./configure
make
wget http://pecl.php.net/get/memcache-2.2.7.tgz
tar zvxf memcache-2.2.7.tgz
cd memcache-2.2.7
phpize
./configure
make -j 4

sudo cp modules/memcache.so /usr/local/lib/php/extensions/no-debug-non-zts-20121212/


php.ini

extension=memcache.so

MySQLのmemcachedインターフェース(InnoDB Integration with memcached)を実験してみた：概要

MySQL

ほぼ完全に備忘録な上に「やった手順に沿って」の内容なんで、激しく長いです(苦笑


先に結論だけ書いておくと
・格納先テーブルは任意に選べる
・keyについては…
　＊charとvarcharあたりが相性がいいんだけど、intもいける
　＊varbinaryが相性悪くて、故に「'A' == 'a'」「'D1' == 'd1'」が成り立つ状況がちょっと怖い
　＊以外なことにintがいける
　＊datetimeとかはまぁ予想通りNG
　＊keyの値のうち、スペース(0x20)はアンダースコアに自動変換される。ために、元々のデータに「スペースを含むkeyの値」がある場合、取得不可能になるので注意
　＊アクセス用の名前(innodb_memcache.containers.name)と値とのセパレータに.(ドット)が用いられているため、keyカラム名にはドットを入れないようにする。値に混入した場合にどうなるかは未調査
・valueについては…
　＊単一カラムの場合、char、varchar、varbinary、text、blob、intが利用可能。datetime系は利用不可
　＊intは使えるけど、戻ってくる値がstringになってるので、微妙に注意
　＊実は「複数カラム」が可能。各カラムのデータは | によって仕切られる。仕切り文字の変更が可能かどうかは不明*1。また、エスケープなどが存在するかは不明。なので、データに「 | が含まれないこと」を保証する必要がありそう
　＊valueを「複数カラム」指定することも可能だが、その場合、varbinaryがまざると使えなくなる
　＊日本語は問題なく使用可能
　＊テーブルを「CHARACTER SET 'binary'」で作ると使用不可になるため、「CHARACTER SET 'utf8mb4'」が無難なところ


あと、未検証だけどいけそうなのが
・多分、複合キーがいけるぽい雰囲気がある


先に雑感。
PKのカラムがintまたはvarcharのテーブルにおいて、いわゆるコードテーブル(マスターテーブル)のreadであれば、利用は比較的容易かもしれない(特に引っ張ってくるべき値が単一カラムの場合)。
それ以外でも「とりあえず見たいだけ」というような、単純なread用途であれば、適宜置き換えるのは、場合によっては有益かもしれない。

おおざっぱに基本

「準備( http://d.hatena.ne.jp/gallu/20140511/p2 )」をすると、innodb_memcache っていうdatabaseが出来るです。
で、ここに
cache_policies
config_options
containers
の３つのテーブルが出来ます。
cache_policiesとconfig_optionsについては、とりあえず「用途不明」。
中を見てるとなんか出来そうなんですが、config_optionsの中身を「軽く変えた」程度だと意図する挙動にはならなかったので、ここは「眺めておく」程度が無難。


んで。
ポイントはcontainersテーブル。
いくつかよく分からんカラムもあるんだけど。
基本的には「(db_schema)にある(db_table)ってテーブルの、(key_columns)をkeyに、(value_columns)をvalueに持ってる」程度の感じ。


テーブルは、一番分かりやすいのを一つ書いておくと、こんな感じ。

create table test1 (
  `key` varchar(256),
  `val` varchar(256),
  `flags` int,
  `cas_column` bigint UNSIGNED,
  `expire_time_column` int,
  PRIMARY KEY(`key`)
);

flags、cas_column、expire_time_columnは一端「おまじない」だと思って頂けると。


このテーブルが、furu_testっていうdatabaseに入っている、と仮定すると。
まず、containersテーブルに、情報をぶちこみます。

insert into innodb_memcache.containers 
   set name='test1', db_schema='furu_test', db_table='test1', key_columns='key', value_columns='val',
       flags='flags', cas_column='cas_column', expire_time_column='expire_time_column', unique_idx_name_on_key='PRIMARY';

nameは「識別用の名前」なんで、ある程度適当に(っても使うから、ある程度は考えて)。
db_schema、db_table、key_columns、value_columnsは前述の通り。
flags、cas_column、expire_time_column、unique_idx_name_on_keyは一端「おまじない」で覚えてください。


そうすると後は

<?php
// 接続
$m = new Memcache();
$r = $m->addServer('localhost', 11211);
// データの取得
$v = $m->get('@@test1.d1');
var_dump($v);
// データの設定
$r = $m->set('@@test1.d1', (string)mt_rand(0,1000));

こんな感じで使えます。
setとかgetとかaddとかの、keyの値を「'@@' + containers.nameの値 + '.' + keyの値」にするのがコツ。

*1:innodb_memcache.config_options が怪しいが、簡単にテストした程度だと、変更は出来なかった

あぁ2038年問題か

MySQL

MySQLにデータを入れようとしたら


Incorrect datetime value


で怒られた。
…なんでだろ？ って思ったら、timestamp型で、'2054-1-1 22:33:44'って値を入れようとしてた。
timestampって「TIMESTAMP データ型の範囲は、'1970-01-01 00:00:01' UTC から '2038-01-19 03:14:07' UTC です」だから、うん、確かにアウトだ(笑


ちっちゃい話なんだけど、意外と忘れそうなので、memo。