MySQL で json オブジェクトを登録

MySQL で json オブジェクトを登録する方法のメモ。

■テーブル定義

create table json1 (
  id   varchar(16) not null,
  obj  json
);

■データ登録

insert into json1 set id = '01', obj = json_array(1, 2, 3);
insert into json1 set id = '02', obj = json_object("key1", 1, "key2", 2, "key3", 3);

■検索

mysql> select * from json1;
+----+-----------------------------------+
| id | obj                               |
+----+-----------------------------------+
| 01 | [1, 2, 3]                         |
| 02 | {"key1": 1, "key2": 2, "key3": 3} |
+----+-----------------------------------+

MySQL で検索結果を csv ファイルに出力

MySQL で検索結果を csv ファイルに出力する方法のメモ。

MySQL で secure_file_priv が有効な場合は、出力ファイルは
secure_file_priv のディレクトリを指定する必要があります。

mysql> show variables like 'secure_file_priv';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+

select 文は以下のようになります。
・fields terminaterd by で ',' を指定
・enclosed by で '"' を指定
・escaped by で '\\' を指定

select
  *
into outfile
  '/var/lib/mysql-files/export.csv'
fields terminated by
  ','
enclosed by
  '"'
esaped by
  '\\'
from
  tbl
;

mysql で重複するレコードを登録しない方法

mysql で重複するレコードを登録しない方法のメモ。
以下のようにテーブルを作成し、エラーにならないように重複するレコードを登録する insert 文を実行します。

create table test1 (
       id    integer not null auto_increment,
       name  varchar(100) not null,

       primary key (id),
       unique key (name)
);

insert into test1 set name = 'name1';
insert into test1 set name = 'name2';
insert into test1 set name = 'name3';

■DB登録内容

mysql> select * from test1;
+----+-------+
| id | name  |
+----+-------+
|  1 | name1 |
|  2 | name2 |
|  3 | name3 |
+----+-------+

■レコード登録
insert ignore into ～ でレコードを登録します。

insert ignore into test1 set name = 'name1';
insert ignore into test1 set name = 'name2';
insert ignore into test1 set name = 'name4';

■DB登録内容確認

mysql> select * from test1;
+----+-------+
| id | name  |
+----+-------+
|  1 | name1 |
|  2 | name2 |
|  3 | name3 |
|  6 | name4 |
+----+-------+

id が name1、name2 分だけインクリメントされています。

次に、テーブルを作り直して、insert ～ where not exists ～ でレコードを登録します。
■レコード登録

insert into test1 (name)
select * from (select 'name1') as tmp
where not exists (select 1 from test1 where name = 'name1');

insert into test1 (name)
select * from (select 'name2') as tmp
where not exists (select 1 from test1 where name = 'name2');

insert into test1 (name)
select * from (select 'name5') as tmp
where not exists (select 1 from test1 where name = 'name5');

■DB登録内容確認

mysql> select * from test1;
+----+-------+
| id | name  |
+----+-------+
|  1 | name1 |
|  2 | name2 |
|  3 | name3 |
|  4 | name5 |
+----+-------+

重複レコードで id がインクリメントされずに、連番になっていることがわかります。

mysql 8.0 でカタカナ、ひらがなを区別して検索する方法

mysql 8.0 でカタカナ、ひらがなを区別して検索する方法のメモ。
mysql 8.0 で、varchar のカラムをひらがなで検索すると、
同じ読みののカタカナ文字列も検索されてしまいました。

以下のように varchar のカラムにひらがなの文字列、カタカナの文字列を登録します。

create table test1 (
       a   varchar(128) not null default '',
       b   varchar(128) not null default '',
       primary key(a, b)
);

replace into test1 set a = 'あいう', b = '123';
replace into test1 set a = 'アイウ', b = '456';

そして、ひらがな、カタカナで検索すると、以下のような結果となります。

mysql> select * from test1 where a = 'あいう';
+-----------+-----+
| a         | b   |
+-----------+-----+
| あいう    | 123 |
| アイウ    | 456 |
+-----------+-----+

mysql> select * from test1 where a = 'アイウ';
+-----------+-----+
| a         | b   |
+-----------+-----+
| あいう    | 123 |
| アイウ    | 456 |
+-----------+-----+

varchar のカラムでは、ひらがなとカタカナを区別せずに検索していることがわかります。

これはこれでありがたい場合がありますが、区別したい場合には binary 属性をつけて varchar カラムを定義します。

create table test2 (
       a   varchar(128) binary not null default '',
       b   varchar(128) binary not null default '',
       primary key(a, b)
);

replace into test2 set a = 'あいう', b = '123';
replace into test2 set a = 'アイウ', b = '456';

ひらがな、カタカナで検索すると文字種を区別した結果が得られます。

mysql> select * from test2 where a = 'あいう';
+-----------+-----+
| a         | b   |
+-----------+-----+
| あいう    | 123 |
+-----------+-----+

mysql> select * from test2 where a = 'アイウ';
+-----------+-----+
| a         | b   |
+-----------+-----+
| アイウ    | 456 |
+-----------+-----+

MySQL で delete 中に select を実行した場合の挙動を確認

MySQL (innodb) で delete 中に select を実行した際に、select がロックされるかを確認したメモ。
以下の例で、select はロックされない、select ... for update はレコード単位でロックされることが確認できました。

以下のテーブルを作成して、レコードを登録しておきます。

create table test_lock (
       id    varchar(100),
       r     int,
       primary key(id),
       index (r)
);

■実験1
以下の delete＋10秒のスリープ のトランザクションを実行中に select を行い、
delete のトランザクション完了する前に select の実行結果が得られるか調べます。

begin;
  delete from test_lock where r >= 900;
  select sleep(10);
commit;

begin;
  select * from test_lock where r >= 900 limit 10;
commit;

実行してみると、delete によってロックされずに select の結果が得られることが確認できました。

■実験2
一方、以下の select ... for update の場合には、delete のトランザクション終了後に
select の実行が完了しました。

begin;
  select * from test_lock where r >= 900 limit 10 for update;
commit;

■実験3
上記の delete、select はともに r >= 900 の条件で同一レコードを参照していますが、
select の条件を変更して delete の対象と重ならないようにすると、delete によってロックされずに
select の結果が得られます。

begin;
  select * from test_lock where r <= 100 limit 10 for update;
commit;

MySQLでランダムにレコードを検索する方法

MySQLでランダムにレコードを検索する方法のメモ。

MySQLでランダムにレコードを検索するには order by rand() を指定します。
例えばランダムにレコードを10件取得するには以下のような select 文になります。

■方法1

select
	*
from
	table
order by
	rand()
limit 10
;

乱数をあらかじめレコードに付与しておくことで、ランダムにレコードを選択することもできます。

■方法2

create table rand (
	id	varchar(100),
	r	float,
	primary key(id),
	index (r)
);

select
        id
from
        rand
where
        r >= rand()
order by
        r asc
limit 10
;

10万件のレコードを登録しておいて、方法1と方法2の実行時間を調べたところ、
方法1は0.06秒、方法2は0.00秒という結果になりました。

mysql で select 文の実行回数を調べる方法

mysql で select 文の実行回数を調べる方法のメモ。

mysql が処理した select 文の実行回数から、その mysql サーバが使われているか、いないかを判定するのに役立ちます。

mysql> show global status like 'com_select';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 40045 |
+---------------+-------+

実際に select 文を実行してみると、実行回数が増えていることが確認できます。

mysql> select 1;
+---+
| 1 |
+---+
| 1 |
+---+

mysql> show global status like 'com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 40046 |
+---------------+-------+

select 文以外の insert、update、delete などについても同様に実行回数を取得することができます。

mysql> show global status like 'com_insert';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| Com_insert    | 77371105 |
+---------------+----------+

mysql> show global status like 'com_update';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| Com_update    | 48450912 |
+---------------+----------+

mysql> show global status like 'com_delete';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Com_delete    | 9098866 |
+---------------+---------+

mysqlのテーブル、カラム名の一覧を調べる方法

mysqlのテーブル、カラム名の一覧を調べる方法のメモ。

mysql のカラム情報は information_schme DB の columns テーブルに記録されています。

mysql> show fields from columns;
+--------------------------+---------------------+------+-----+---------+-------+
| Field                    | Type                | Null | Key | Default | Extra |
+--------------------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG            | varchar(512)        | YES  |     | NULL    |       |
| TABLE_SCHEMA             | varchar(64)         | NO   |     |         |       |
| TABLE_NAME               | varchar(64)         | NO   |     |         |       |
| COLUMN_NAME              | varchar(64)         | NO   |     |         |       |
| ORDINAL_POSITION         | bigint(21) unsigned | NO   |     | 0       |       |
| COLUMN_DEFAULT           | longtext            | YES  |     | NULL    |       |
| IS_NULLABLE              | varchar(3)          | NO   |     |         |       |
| DATA_TYPE                | varchar(64)         | NO   |     |         |       |
| CHARACTER_MAXIMUM_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| CHARACTER_OCTET_LENGTH   | bigint(21) unsigned | YES  |     | NULL    |       |
| NUMERIC_PRECISION        | bigint(21) unsigned | YES  |     | NULL    |       |
| NUMERIC_SCALE            | bigint(21) unsigned | YES  |     | NULL    |       |
| CHARACTER_SET_NAME       | varchar(32)         | YES  |     | NULL    |       |
| COLLATION_NAME           | varchar(32)         | YES  |     | NULL    |       |
| COLUMN_TYPE              | longtext            | NO   |     | NULL    |       |
| COLUMN_KEY               | varchar(3)          | NO   |     |         |       |
| EXTRA                    | varchar(27)         | NO   |     |         |       |
| PRIVILEGES               | varchar(80)         | NO   |     |         |       |
| COLUMN_COMMENT           | varchar(255)        | NO   |     |         |       |
+--------------------------+---------------------+------+-----+---------+-------+

以下のテーブルを作成します。

drop table if exists test1;
create table test1 (
       col1  varchar(256),
       col2  varchar(256)
);

drop table if exists test2;
create table test2 (
       col1  varchar(256),
       col2  varchar(256)
);

上記のテーブル情報を確認できます。

mysql> select table_name, column_name from information_schema.columns
    -> where table_schema = '{DB名}';
+------------+-------------+
| table_name | column_name |
+------------+-------------+
| test1      | col1        |
| test1      | col2        |
| test2      | col1        |
| test2      | col2        |
+------------+-------------+

mysql の group by で積を計算する方法

mysql の group by で積を計算する方法のメモ。

group by を使えば sum() で和を計算することはできますが、積を計算する関数はありませんが、
以下のように log10() と pow() を使えば、積を計算することができます。

create table pro (
       id    integer,
       val   float
);

insert into pro set id = 1, val = 2;
insert into pro set id = 1, val = 3;
insert into pro set id = 1, val = 4;
insert into pro set id = 1, val = 5;

insert into pro set id = 2, val = 2;
insert into pro set id = 2, val = 3;
insert into pro set id = 2, val = 4;

select
        id
        , pow(10, sum(log10(val)))
from
        pro
group by
        id
;

実行結果は以下の通りです。

id      pow(10, sum(log10(val)))
1       120
2       24

積が計算できていることがわかります。

mysql でクライアントからの新規接続を受け付けなくする方法

mysql でクライアントからの新規接続を受け付けなくする方法のメモ。

set global offline_mode = 1;

を実行すると、クライアントからの新規の接続は受け付けず、
実行中の処理は処理が完了するまで実行してくれます。

mysqldump したデータを取り込んだ場合の外部キー制約に関する検証のメモ

mysqldump したデータを取り込んだ場合の外部キー制約に関する検証のメモ。

外部キー制約の関係がある 2つのテーブルを条件指定つきで mysqldump で出力して、
その結果をロードした場合に、外部キー制約が効いているかを検証してみました。

結果としては、外部キー制約が効いていないレコードが登録された状態になります。

まず、以下の2つのテーブルを定義します。
constraint1 テーブルには id だけを登録します。
constraint2 テーブルには、constraint1 テーブルに id が登録されている id に対してのみ、
val を登録します。

create table constraint1 (
       id    int not null,
       primary key(id)
) ENGINE=InnoDB;

create table constraint2 (
       id    int not null,
       val   varchar(20) not null,

       primary key(id),
       foreign key (id)
               references constraint1(id)
               on delete cascade
) ENGINE=InnoDB;

insert into constraint1 set id = 1;
insert into constraint1 set id = 2;
insert into constraint1 set id = 3;
insert into constraint1 set id = 4;
insert into constraint1 set id = 5;

insert into constraint2 set id = 1, val = "val1";
insert into constraint2 set id = 2, val = "val2";
insert into constraint2 set id = 3, val = "val3";
insert into constraint2 set id = 4, val = "val4";
insert into constraint2 set id = 5, val = "val5";

各テーブルには以下のようにレコードが登録されています。

mysql> select * from constraint1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.00 sec)

mysql> select * from constraint2;
+----+------+
| id | val  |
+----+------+
|  1 | val1 |
|  2 | val2 |
|  3 | val3 |
|  4 | val4 |
|  5 | val5 |
+----+------+
5 rows in set (0.00 sec)

ここで、constraint1 のテーブルから id=5 のレコードを削除すると、
constraint2 のテーブルからも id=5 のレコードが削除されます。

mysql> delete from constraint1 where id = 5;
Query OK, 1 row affected (0.01 sec)

mysql> select * from constraint1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.00 sec)

mysql> select * from constraint2;
+----+------+
| id | val  |
+----+------+
|  1 | val1 |
|  2 | val2 |
|  3 | val3 |
|  4 | val4 |
+----+------+
4 rows in set (0.00 sec)

この状態で以下の mysqldump コマンドでテーブルを dump します。

mysqldump -u{id} -p{password} {db} constraint1 -w 'id <= 2' > dump_constraint1_2.sql
mysqldump -u{id} -p{password} {db} constraint2

constraint1 は id=1, 2 のレコードしか抽出しません。
一方、constraint2 は id=3, 4 のレコードも抽出します。
これらのデータを取り込んだ際に、constraint2 で id=3,4 のレコードが登録されるかを確認します。

cat dump_constraint1_2.sql | mysql -u{id} -p{password} {db}
cat dump_constraint2.sql | mysql -u{id} -p{password} {db}

結果は以下の通り。

mysql> select * from constraint1;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

mysql> select * from constraint2;
+----+------+
| id | val  |
+----+------+
|  1 | val1 |
|  2 | val2 |
|  3 | val3 |
|  4 | val4 |
+----+------+
4 rows in set (0.00 sec)

constraint1 に登録されていない id のレコードが、constraint2 にも登録された状態になります。

mysqlで疑似的にtrieを実現する方法

mysqlで疑似的にtrieを実現する方法のメモ。

ウェブサービスで、処理対象とする URL が何らかの除外したい URL に前方一致でマッチするかを判定したい場合、
除外URLの数が多い場合、効率よく判定を行う必要があります。

例えば、test.com/abcdef/ghi が mysql に登録されたURLにマッチするかを調べる場合、
以下のような select 文で前方一致の検索を行うことができます。

select * from {table名} where 'test.com/abcdef/ghi' like concat({URLのカラム名}, '%');

しかし、上記の select 文では、mysql に登録されている各レコードの URL カラムに対して
前方一致で文字列マッチの判定が行われ、レコード数分の検索が行われることになります。
そのため、レコード数に応じて処理速度が低下します。


以下は、mysql で trie 相当の機能を実現する方法案です。
ある URL が、除外したい URL にマッチするかどうかの判定を行う例で考えます。

まず、除外URL に URL に使用されない文字を追加して固定文字列長の URL を生成します。
例えば、除外URL の文字列長が15文字で、除外 URL が test.com/abc の場合、
URLに使用されない文字として '@' を追加して以下のような文字列を生成し、DBに登録します。

test.com/abc@@@

判定対象の URL (= 'test.com/abcdefg') が除外対象の URL かを判定するために、
以下の検索式でテーブルを検索します。

select * from {テーブル名} where {URLのカラム名} regexp '^t[e@][s@][t@][\.@][c@][o@][m@][/@][a@][b@][c@][d@][e@][@f][@g]';

正規表現の ^ で URL の文字列の先頭にマッチさせ、次の t は URL の先頭文字列です。
前方一致でのマッチを行うため、一文字目は判定対象の URL の先頭文字の t が
必ず入っていることを想定しています。

次の [e@] は "test.com の e または 使用されない文字 @" にマッチする場合で、
前方一致の URL として te または t@ が登録されている場合にマッチします。
t@ にマッチするのは前方一致の URL が t のみの場合で、DBに登録された値が "t"+"@"x15 の場合に相当します。
[s@] 以降も同様です。


以下、検証です。

URLのテーブルとして、以下を定義して適当にテスト用のURLを登録します。

create table url_trie_test (
       url   varchar(512) not null,
       index(url)
);

そして、以下の explain を実行します。

explain select * from url_trie_test where url regexp '^t[e@][s@][t@][\.@][c@][o@][m@][/@][a@][b@][c@][d@][e@][f@][g@][h@][i@][j@][/@][k@][l@][m@]'\G

すると、explain の結果は以下のようになり、インデックスが使われた検索になっているようです。

  select_type: SIMPLE
        table: url_trie_test
         type: index
possible_keys: NULL
          key: url
      key_len: 514
          ref: NULL
         rows: 2691
        Extra: Using where; Using index

前方一致の url として約2700件が登録されている状態で、上記の select 文を実行すると、以下のようになりました。

mysql> select * from url_trie_test where url regexp '^t[e@][s@][t@][\.@][c@][o@][m@][/@][a@][b@][c@][d@][e@][f@][g@][h@][i@][j@][/@][k@][l@][m@]'\G
*************************** 1. row ***************************
url: test.com/abc@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
1 row in set (0.08 sec)

mysqlのインデックスを使って、疑似的に trie の検索ができているようです。

mysql で日時を数値で取得する方法

mysql で日時を数値で取得する方法のメモ。

日時を数値で取得する場合に、unix_timestamp() を使うと数値で日時を取得できます。

select unix_timestamp('2019-09-16 00:00:00');

1568559600

しかし、unix_timestamp() だと2038年問題があり、期待した結果が得られないことがあります。

select unix_timestamp('9999-01-01 00:00:00');

0

以下のようにすれば、日時を数値で取得することができます。

select cast(date_format('9999-01-01 00:00:00', '%Y%m%d%H%i%s') as decimal(14));

99990101000000

年が4桁を超える場合には、decimal(14) の 14 を増やす必要がありますが。

mysql で遅いクエリを調べる方法

mysql で遅いクエリを調べる方法のメモ。

show processlist で実行中のクエリを見ることはできますが、
実行時間順にソートされていません。

performance DBの threads テーブルに対して、processlist_time でソートすると
実行時間順でソートした検索ができます。

mysql> select * from performance_schema.threads order by processlist_time asc\G

*************************** 1. row ***************************
          THREAD_ID: 3533337
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 3533304  ★★★
   PROCESSLIST_USER: XXXXXXXX
   PROCESSLIST_HOST: XXX.XXX.XXX.XXX
     PROCESSLIST_DB: XXXXXXXXXXXXX
PROCESSLIST_COMMAND: Query
   PROCESSLIST_TIME: 1540  ★★★
  PROCESSLIST_STATE: Creating sort index
   PROCESSLIST_INFO: ... 
...

遅いクエリを終了させたければ、PROCESSLIST_ID を指定して kill すればOKです。

mysql> kill 3533304;

mysqlでスレッドの情報をみる方法

mysql で実行中のスレッドの情報をみる方法のメモ。

top で mysql の特定のスレッドの CPU 使用率が高い場合に、
そのスレッドが何を実行しているかを調べる場合には、以下の様にします。

mysql> select * from performance_schema.threads where THREAD_OS_ID={OSのスレッドID}\G

*************************** 1. row ***************************
          THREAD_ID: 1293224
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 1293191
   PROCESSLIST_USER: {ユーザ名}
   PROCESSLIST_HOST: {ホスト名}
     PROCESSLIST_DB: {DB名}
PROCESSLIST_COMMAND: Query
   PROCESSLIST_TIME: 0
  PROCESSLIST_STATE: starting
   PROCESSLIST_INFO: INSERT INTO ... 
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: SSL/TLS
       THREAD_OS_ID: {OSのスレッドID}
1 row in set (0.01 sec)

PROCESSLIST_INFO に実行中のSQL文が表示されます。