問題 InnoDBエンジンのあるテーブルに、insert into T select * from XXX 系のSQLを流すと、AUTO_INCREMENTの連番が余分に消費されて、次に挿入するレコードにはかなり飛んだ番号が付きました。 1,2,3,4,5,6,7,8,9,10 の10行が入った後で、次の行を入れると連番が16になっているなど。 なぜでしょう。 答え より単純な再現手順。 insertは2行のはずだが、連番が3進む。1つ余分に進む。 /* AUTO_INCREMENTのカラムを持つテーブルを作成 */ create table T ( id int primary key auto_increment, code varchar(10) not null ) engine=InnoDB; /* insert select をする. code 'a' と 'b' の行を追加す

問題 Covering Index って何ですか。 答え Covering index Covering index は索引中にキーではない列を含める方式である。もし索引を使う検索が、行全体ではなく、キーと幾つかの列のみを必要とする場合、その必要とされる列が索引のデータ構造内にあれば、検索は索引内で完結できる。表からデータを読み取る必要が無いため効率が良い。 Covering index は表のサイズがメモリに保持しきれないほど大きい場合の検索で有効であるが、索引のサイズは増加することに注意が必要である。また、キーでない列の値が変更された際にも索引を更新する必要があるため、更新の性能は低下する傾向がある。 では、MySQL5.5 InnoDBのテーブルにて実験する。 実験の準備 こんなテーブルを用意する。 mysql> create table item ( -> item_id int

問題 データベースを丸ごと複製するには？ 答え ダンプをとって、別の名前のデータベースに投入する。 mysqldump -u username -p db_name > db_name.sql mysql -u username -p db_name2 < db_name.sql どうしても1行で書きたいときは、ダンプデータをパイプでmysqlに渡す。 mysqldump -u username -ppassword db_name1 | mysql -u username -ppassword db_name2

普段意識することはないですが、MySQLのクエリの長さには制限値があります。 ● どこで分かるの？ → /etc/my.cnf に書いてある。 max_allowed_packet という設定項目。 こんな感じで書いてあります。 max_allowed_packet=32M 設定ファイルを変えて MySQL再起動で、制限値を変えることが可能です。 ●サーバーにログインできないから見えないんだけど？ →MySQLの各種設定値、変数は SHOW VARIAVLES で見ることができる。 mysql> show variables like 'max_allowed_packet'; +--------------------+----------+ | Variable_name | Value | +--------------------+----------+ | max_allowed

問題 SQLで、年度末ってどうやって計算すればよいですか？（4月区切りの） 4月以前だったら～、4月以降だったら～みたいな条件分岐が必要？ 答え 4月以前、以降で場合分けをするのは、素直なアイデアで間違ってはいない。 書き方はいろいろと考えられる。 SELECT CONCAT((YEAR(CURRENT_DATE()) + (MONTH(CURRENT_DATE()) >= 4)), '-03-31') SELECT CONCAT((YEAR('2000-01-01') + (MONTH('2000-01-01') >= 4)), '-03-31') SELECT DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL IF(MONTH(CURDATE()) >= 4, 1, 0) YEAR), '%Y-03-31') SELECT DATE_FORMAT(DAT

問題 select * from member where namae like '%サトウ%'; こんなSQLで、namaeがサトウ、ｻﾄｳ、さとう、サトｳ（一部半角）何でもマッチさせたい！ 答え では、これで。 select * from member where namae collate utf8_unicode_ci like '%サトウ%'; データベースがutf8でないときは、もうひとつ変換を入れて、 /* ERROR 1253: COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'ujis' など言われたら */ select * from member where convert(namae using utf8) collate utf8_unicode_ci like '%サトウ%'; 数字の全角/半

問題 大量のテスト用データのあるテスト環境を作りたいんだけど、大量のデータを作るのって大変ですか？ 答え 100万件レベルのデータのテストデータをさくっと作成する方法。 MySQLの場合。 primary key が AUTO_INCREMENT なら # テーブル作る create table a (id int primary key auto_increment, xxx varchar(10)); # 1レコード作る insert into a () values (); # 倍々する insert into a (id) select 0 from a; -- 2行になる insert into a (id) select 0 from a; -- 4行になる insert into a (id) select 0 from a; -- 8行になる insert into a (

問題 日本語を含むSQLを実行したいんだけど、できない！ ssh端末で日本語が入力できない。 日本語が化ける。 文字コードが合わなくて通じない（ソースはUTF-8、データベースはEUC）。 答え MySQLだと、HEX関数、CHAR関数を使ったこんな手がある。 EUC-JPで「内藤さん」を探したいとき。 １、どこか日本語が通じるところで下ごしらえ select hex('内藤'); → C6E2C6A3 「内藤」のujisでの16進表現が確認できた。 下ごしらえはphpでやってもよい。 //EUC-JPのソースで var_dump(unpack('H*', '内藤')); //→ c6e2c6a3 ２、問題の環境でSQL実行 16進表現を元の文字列に戻したり（0xつける） select * from dtb_customer where name01 = char(0xC6E2C6A3);

問題 テキスト型のカラムにカンマ区切りのデータが入っているらしいんだけど、like検索するしかないかな？ 答え　１ MySQLならでは。FIND_IN_SET関数がある。 FIND_IN_SET(str, strlist) N 個の部分文字列で構成されるリスト strlist に、文字列 str が含まれている場合は、1 から N までのいずれかの値を返す。文字列のリストは、それぞれの間を ‘,’ 文字で区切られた各部分文字列で構成される文字列である。最初の引数が定数文字列で、2 番目の引数が SET 型のカラムの場合、FIND_IN_SET() 関数はビット演算を使用するよう最適化される。 str が strlist に含まれていない場合や、strlist が空の文字列の場合は、0 を返す。どちらの引数も NULL の場合は、NULL を返す。 最初の引数にカンマ ‘,’ が含まれている

問題 MySQLで、ランダムに行を取り出したい。 order by rand() でいいの？ 答え よくあるのがこんな方法。 select * from t order by rand() limit 10; レコード数が少ない場合は問題ない。 レコード数が何万行とある場合は大問題。order by句にrand()を使うと、全レコード数分のrand()を算出して、全レコードでソートをかける。ものすごく大変そうなのは容易に想像できる。 そこでwhere句にrand()を使う方法。 10万レコードから100件欲しいときは、100/100000 = 0.001なので、こうすると理論上100件取れる。 select * from t where rand() <= 0.001; 理屈はそうだけど、実際はちょっと余裕を持たせて、こうするとよい。 select * from t where rand(

【php】 TOTPのワンタイムパスワードを生成する 問題 TOTPのワンタイムパスワードをphpで生成したいです。 答え 例 // ライブラリを利用するか、関数を自作してbase32_decodeしてください $binary_secret = Base32::decode […] 【php】base32_decode() 問題 phpで、base32_decode() したいです。 答え 例 function base32_decode($s) { $m = array( "A" => 0, "B" => 1, "C" => 2, "D" […] 【windows】スタートメニューやタスクバーのフォルダ 問題 スタートメニューやタスクバーのショートカットなどは、どこかのディレクトリに入ってますか？ 答え スタートメニュー（全体） C:\ProgramData\Microsoft\Wind