MySQL で大文字と小文字を区別しないIDを登録

MySQL で大文字と小文字を区別しないIDを登録する方法のメモ。 MySQL で大文字と小文字を区別しないIDを生成するには、以下のように collate を指定します。

create table test_collate1 (
  id   varchar(32) not null collate utf8mb4_0900_ai_ci,
  primary key (id)
);

insert ignore into test_collate2 (id) values ('abC123');
insert ignore into test_collate2 (id) values ('Abc123');
insert ignore into test_collate2 (id) values ('deF456');
insert ignore into test_collate2 (id) values ('DEF456');

上記を実行すると、大文字・小文字を区別せずにユニークな 'abC123' と 'deF456' のみがテーブルに登録されます。

mysql> select  * from test_collate2;
+--------+
| id     |
+--------+
| abC123 |
| deF456 |
+--------+

MySQL で過去レコードを削除するイベントを登録

MySQL で過去レコードを削除するイベントを登録する方法のメモ。

■テーブル作成・データ登録

drop table if exists test_event1;

create table test_event1 (
  id   varchar(16)  not null,
  t    timestamp,
  primary key(id),
  index (t)
);

insert into test_event1 (id, t) values ('01', '2024-04-18 23:43:00');
insert into test_event1 (id, t) values ('02', '2024-04-18 23:43:30');
insert into test_event1 (id, t) values ('03', '2024-04-18 23:44:00');
insert into test_event1 (id, t) values ('04', '2024-04-18 23:44:30');
insert into test_event1 (id, t) values ('05', '2024-04-18 23:45:00');
insert into test_event1 (id, t) values ('06', '2024-04-18 23:45:30');
insert into test_event1 (id, t) values ('07', '2024-04-18 23:46:00');
insert into test_event1 (id, t) values ('08', '2024-04-18 23:46:30');
insert into test_event1 (id, t) values ('09', '2024-04-18 23:47:00');
insert into test_event1 (id, t) values ('10', '2024-04-18 23:47:30');

■イベント登録

5分経過したレコードを削除するイベントを登録。

drop event if exists event1;

create event event1
on schedule
  every 1 minute
do
  delete from
    test_event1
  where
    t < date_sub(now(), interval 5 minute)
;

■時間が経過するとレコードが削除される

mysql> select now(), id, t from test_event1;
+---------------------+----+---------------------+
| now()               | id | t                   |
+---------------------+----+---------------------+
| 2024-04-18 23:47:08 | 01 | 2024-04-18 23:43:00 |
| 2024-04-18 23:47:08 | 02 | 2024-04-18 23:43:30 |
| 2024-04-18 23:47:08 | 03 | 2024-04-18 23:44:00 |
| 2024-04-18 23:47:08 | 04 | 2024-04-18 23:44:30 |
| 2024-04-18 23:47:08 | 05 | 2024-04-18 23:45:00 |
| 2024-04-18 23:47:08 | 06 | 2024-04-18 23:45:30 |
| 2024-04-18 23:47:08 | 07 | 2024-04-18 23:46:00 |
| 2024-04-18 23:47:08 | 08 | 2024-04-18 23:46:30 |
| 2024-04-18 23:47:08 | 09 | 2024-04-18 23:47:00 |
| 2024-04-18 23:47:08 | 10 | 2024-04-18 23:47:30 |
+---------------------+----+---------------------+

mysql> select now(), id, t from test_event1;
+---------------------+----+---------------------+
| now()               | id | t                   |
+---------------------+----+---------------------+
| 2024-04-18 23:48:45 | 03 | 2024-04-18 23:44:00 |
| 2024-04-18 23:48:45 | 04 | 2024-04-18 23:44:30 |
| 2024-04-18 23:48:45 | 05 | 2024-04-18 23:45:00 |
| 2024-04-18 23:48:45 | 06 | 2024-04-18 23:45:30 |
| 2024-04-18 23:48:45 | 07 | 2024-04-18 23:46:00 |
| 2024-04-18 23:48:45 | 08 | 2024-04-18 23:46:30 |
| 2024-04-18 23:48:45 | 09 | 2024-04-18 23:47:00 |
| 2024-04-18 23:48:45 | 10 | 2024-04-18 23:47:30 |
+---------------------+----+---------------------+

mysql> select now(), id, t from test_event1;
+---------------------+----+---------------------+
| now()               | id | t                   |
+---------------------+----+---------------------+
| 2024-04-18 23:49:39 | 05 | 2024-04-18 23:45:00 |
| 2024-04-18 23:49:39 | 06 | 2024-04-18 23:45:30 |
| 2024-04-18 23:49:39 | 07 | 2024-04-18 23:46:00 |
| 2024-04-18 23:49:39 | 08 | 2024-04-18 23:46:30 |
| 2024-04-18 23:49:39 | 09 | 2024-04-18 23:47:00 |
| 2024-04-18 23:49:39 | 10 | 2024-04-18 23:47:30 |
+---------------------+----+---------------------+

MySQL の check 制約

MySQL の check によるデータの制約を確認します。
以下は test_check_01 テーブルで、num が 1 <= num <= 10、str の文字列長 <= 5 の制約を設定しています。

■テーブル定義・データ登録

create table test_check_01 (
  id        varchar(32) not null,
  num       integer not null,
  str       varchar(8),

  primary key(id),
  check(num >= 1 and num <= 10),
  check(char_length(str) <= 5)
);

insert into test_check_01 (id, num, str) values ('id_1', 1, 'abc');
insert into test_check_01 (id, num, str) values ('id_2', 2, 'def');
insert into test_check_01 (id, num, str) values ('id_3', 3, 'ghi');

■check の条件の確認

mysql> select * from test_check_01;
+------+-----+------+
| id   | num | str  |
+------+-----+------+
| id_1 |   1 | abc  |
| id_2 |   2 | def  |
| id_3 |   3 | ghi  |
+------+-----+------+

mysql> update test_check_01 set num = num * 4;;
ERROR 3819 (HY000): Check constraint 'test_check_01_chk_1' is violated.

mysql> select * from test_check_01;
+------+-----+------+
| id   | num | str  |
+------+-----+------+
| id_1 |   1 | abc  |
| id_2 |   2 | def  |
| id_3 |   3 | ghi  |
+------+-----+------+

mysql> update test_check_01 set str = concat(str, '___');
ERROR 3819 (HY000): Check constraint 'test_check_01_chk_2' is violated.

mysql> select * from test_check_01;
+------+-----+------+
| id   | num | str  |
+------+-----+------+
| id_1 |   1 | abc  |
| id_2 |   2 | def  |
| id_3 |   3 | ghi  |
+------+-----+------+
3 rows in set (0.00 sec)

MySQL 8.0 でカタカナ・ひらがなを区別する COLLATE の設定

以前、MySQL 8.0 でカタカナ、ひらがなを区別して検索できるようにするために、以下のブログで varchar binary の検証を行いました。
「mysql 8.0 でカタカナ、ひらがなを区別して検索する方法」

今回は COLLATE で、ひらがな・カタカナを区別する方法の検証を行います。

COLLATE が utf8mb4_0900_ai_ci の場合、ひらがなとカタカナを区別しません。

mysql> select @@collation_database;
+----------------------+
| @@collation_database |
+----------------------+
| utf8mb4_0900_ai_ci   |
+----------------------+

mysql> select strcmp('あいう', 'アイウ');
+----------------------------------+
| strcmp('あいう', 'アイウ')       |
+----------------------------------+
|                                0 |
+----------------------------------+

COLLATE が utf8mb4_ja_0900_as_cs_ks の場合には、ひらがなとカタカナを区別することができます。

mysql> select strcmp('あいう' collate utf8mb4_ja_0900_as_cs_ks, 'アイウ' collate utf8mb4_ja_0900_as_cs_ks);
+----------------------------------------------------------------------------------------------------+
| strcmp('あいう' collate utf8mb4_ja_0900_as_cs_ks, 'アイウ' collate utf8mb4_ja_0900_as_cs_ks)       |
+----------------------------------------------------------------------------------------------------+
|                                                                                                 -1 |
+----------------------------------------------------------------------------------------------------+

alter database でデータベースの COLLATE を utf8mb4_ja_0900_as_cs_ks に変更します。

mysql> alter database collate 'utf8mb4_ja_0900_as_cs_ks';
Query OK, 1 row affected (0.01 sec)

mysql> select @@collation_database;
+--------------------------+
| @@collation_database     |
+--------------------------+
| utf8mb4_ja_0900_as_cs_ks |
+--------------------------+

テーブルを作成し、ひらがな・カタカナの文字列を登録します。

mysql> create table test1 ( str varchar(8) );
Query OK, 0 rows affected (0.04 sec)

mysql> insert into test1 values ('あいう'), ('アイウ');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

以下のように、ひらがなとカタカナを区別して検索することができます。
mysql> select * from test1 where str = 'あいう';
+-----------+
| str |
+-----------+
| あいう |
+-----------+

mysql> select * from test1 where str = 'アイウ';
+-----------+
| str |
+-----------+
| アイウ |
+-----------+

MySQL の insert ... select で未登録のレコードを登録

MySQL の insert ... select で、insert 先のテーブルに存在しないレコードを登録する方法のメモ。

■登録元テーブル（test_id_val1）

create table test_id_val1 (
  id         varchar(32) not null,
  val        varchar(32) not null,

  primary key (id)
);

insert into
  test_id_val1
  (id, val)
values
  ('id_01', 'val_01')
  , ('id_02', 'val_02')
  , ('id_03', 'val_03')
  , ('id_04', 'val_04')
  , ('id_05', 'val_05')
  , ('id_06', 'val_06')
;

実行後のテーブルの登録内容は以下の通り。

mysql> select * from test_id_val1;
+-------+--------+
| id    | val    |
+-------+--------+
| id_01 | val_01 |
| id_02 | val_02 |
| id_03 | val_03 |
| id_04 | val_04 |
| id_05 | val_05 |
| id_06 | val_06 |
+-------+--------+

■登録先テーブル（test_insert1）

create table test_insert1 (
  id         varchar(32) not null,
  id_rev     varchar(32) default null,

  primary key (id),
  index (id_rev)
);

insert into
  test_insert1
  (id)
values
  ('id_01'),
  ('id_02'),
  ('id_03')
;

update
  test_insert1
set
  id_rev = reverse(id)
where
  id_rev is null
;

実行後のテーブルの登録内容は以下の通り。

mysql> select * from test_insert1;
+-------+--------+
| id    | id_rev |
+-------+--------+
| id_01 | 10_di  |
| id_02 | 20_di  |
| id_03 | 30_di  |
+-------+--------+

■登録元テーブル（test_id_val1）の id を登録先テーブル（test_insert1）に登録

insert ignore into
  test_insert1
  (id, id_rev)
select
  t1.id
  , reverse(t1.id)
from
  test_id_val1 as t1
left join
  test_insert1 t2
on
  t2.id = t1.id
where
  t2.id is null
;

上記の insert ... select によって test_insert1 に登録されていなかった id_04 ～ id_06 が登録されまています。

mysql> select * from test_insert1;
+-------+--------+
| id    | id_rev |
+-------+--------+
| id_01 | 10_di  |
| id_02 | 20_di  |
| id_03 | 30_di  |
| id_04 | 40_di  |
| id_05 | 50_di  |
| id_06 | 60_di  |
+-------+--------+

MySQL で実行中のクエリを表示

MySQL で実行中のクエリを表示する方法のメモ。
以下のように show processlist で実行中のクエリを表示することができます。

show [full] processlist;

この他に、information_schema の PROCESSLIST テーブルで確認することもできます。

mysql> select * from information_schema.PROCESSLIST\G
*************************** 1. row ***************************
     ID: 9
   USER: root
   HOST: localhost
     DB: NULL
COMMAND: Query
   TIME: 176
  STATE: User sleep
   INFO: select sleep(10000)
*************************** 2. row ***************************
     ID: 8
   USER: root
   HOST: localhost
     DB: NULL
COMMAND: Sleep
   TIME: 173
  STATE:
   INFO: NULL

INFO が実行中のクエリです。
通常のテーブルと同様に各カラムに対して条件を指定して、実行中のクエリを絞り込むことができます。

mysql> select USER, COMMAND, INFO from information_schema.PROCESSLIST where COMMAND != 'Sleep'\G
*************************** 1. row ***************************
   USER: root
COMMAND: Query
   INFO: select sleep(100)

mysql コマンドに -c オプションを指定すると、クエリ内のコメントがそのまま INFO に表示されますので、コメントを使ったクエリ絞り込みを考えてみます。。

*************************** 1. row ***************************
     ID: 8
   USER: root
   HOST: localhost
     DB: NULL
COMMAND: Query
   TIME: 66
  STATE: User sleep
   INFO: select sleep(100) # QUERY: SLEEP_100
*************************** 2. row ***************************
     ID: 9
   USER: root
   HOST: localhost
     DB: NULL
COMMAND: Query
   TIME: 3
  STATE: User sleep
   INFO: select sleep(50) # QUERY: SLEEP_50

# QUERY_TYPE: {クエリ種別} の形式のコメントから {クエリ種別} を抽出します。

mysql> select TIME, regexp_substr(INFO, '# QUERY: ([^\r\n]+)') as QUERY_TYPE, INFO from information_schema.PROCESSLIST where COMMAND != 'Sleep'\G

*************************** 1. row ***************************
      TIME: 81
QUERY_TYPE: # QUERY: SLEEP_100
      INFO: select sleep(100) # QUERY: SLEEP_100
*************************** 2. row ***************************
      TIME: 6
QUERY_TYPE: # QUERY: SLEEP_50
      INFO: select sleep(50) # QUERY: SLEEP_50

上記の QUERY_TYPE で group by するとどんなクエリがどれだけ実行されているかを集計することもできます。

mysql> select regexp_substr(INFO, '# QUERY: ([^\r\n]+)') as QUERY_TYPE, count(1) as cnt from information_schema.PROCESSLIST where COMMAND != 'Sleep' group by QUERY_TYPE\G

*************************** 1. row ***************************
QUERY_TYPE: # QUERY: SLEEP_100
       cnt: 2
*************************** 2. row ***************************
QUERY_TYPE: # QUERY: SLEEP_50
       cnt: 1

MySQL のクエリの最大実行時間の指定

MySQL でクエリの最大実行時間を指定する方法のメモ。
クエリに /*+ max_execution_time(N) */ を指定すると、N msec で最大実行時間を指定することができます。

■最大実行時間を指定しない場合

mysql> select sleep(10);
+-----------+
| sleep(10) |
+-----------+
|         0 |
+-----------+
1 row in set (10.00 sec)

■最大実行時間を指定した場合

mysql> select /*+ max_execution_time(5000) */ sleep(10);
+-----------+
| sleep(10) |
+-----------+
|         1 |
+-----------+
1 row in set (5.00 sec)

最大実行時間で打ち切られています。

mysql で json データを検索

mysql で json データを検索する方法のメモ。

■テーブル定義

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

  primary key (id)
);

■データ登録

insert into json5 set
 id = '01',
 obj = '{"id": "01", "items": [{"name": "name01_01", "value": "value01_01"}, {"name": "name01_02", "value": "value01_02"}]}'
;

insert into json5 set
 id = '02',
 obj = '{"id": "02", "items": [{"name": "name02_01", "value": "value02_01"}, {"name": "name02_02", "value": "value02_02"}, {"name": "name02_03", "value": "value02_03"}]}'
;

■検索
以下では、json_search() を使って検索しています。
json_search() には以下のようにパラメータを指定します。
json_search(json_doc, one_or_all, search_str[, escape_char[, path] ...)

select
  json_pretty(obj)
from
  json5
where
  json_search(obj, 'one', 'name01_02', null, '$.items[*].name')
\G

■検索結果

*************************** 1. row ***************************
json_pretty(obj): {
  "id": "01",
  "items": [
    {
      "name": "name01_01",
      "value": "value01_01"
    },
    {
      "name": "name01_02",
      "value": "value01_02"
    }
  ]
}

MySQL の Connection Pool

python の MySQL ライブラリでは、コネクションプールにコネクションがなくなると queue の例外が発生するため、例外を発生させずにブロックして待ち続けるか、タイムアウトさせるかを指定できるようにしてみました。

ブロックするには MySQLTimeoutConnectionPool(block=True) を指定します。
また、タイムアウトさせるには MySQLTimeoutConnectionPool(block=True, timeout=秒数) を指定します。

mysql-connector-python のプログラムを流用しています。
https://github.com/mysql/mysql-connector-python/blob/master/lib/mysql/connector/pooling.py

■ライブラリ

# -*- coding:utf-8 -*-
#
# MySQL Timeout Connection Pool
#

import threading
import queue
from mysql.connector.pooling import MySQLConnectionPool
from mysql.connector.pooling import PooledMySQLConnection
from mysql.connector.errors import PoolError


class MySQLTimeoutConnectionPool(MySQLConnectionPool):
    def __init__(self,
                 block=True, timeout=0,
                 pool_size=5, pool_name=None,
                 pool_reset_session=True,
                 **kwargs):
        self.block = block
        self.timeout = timeout
        super().__init__(pool_size=pool_size, pool_name=pool_name,
                         pool_reset_session=pool_reset_session,
                         **kwargs)


    def get_connection(self):
        with threading.RLock():
            try:
                cnx = self._cnx_queue.get(block=self.block,
                                          timeout=self.timeout)
            except queue.Empty as err:
                raise PoolError("Failed getting connection; pool exhausted") from err

            if (
                not cnx.is_connected()
                or self._config_version != cnx.pool_config_version
            ):
                cnx.config(**self._cnx_config)
                try:
                    cnx.reconnect()
                except InterfaceError:
                    self._queue_connection(cnx)
                    raise
                cnx.pool_config_version = self._config_version

            return PooledMySQLConnection(self, cnx)

■テスト用プログラム
コネクションを取得できない場合、5秒でタイムアウトさせます。

# -*- coding:utf-8 -*-

import sys
import time
sys.path.append('../../lib')
from mysql_timeout_connection_pool import MySQLTimeoutConnectionPool

config = {
    'host': '127.0.0.1',
    'port': 3306,
    'database': 'test1',
    'user': 'root',
    'password': None,
    'pool_size': 2,
    'block': True,
    'timeout': 5,
}

def main():
    conn_pool = MySQLTimeoutConnectionPool(
        host=config['host'],
        port=config['port'],
        database=config['database'],
        user=config['user'],
        password=config['password'],
        pool_size=config['pool_size'],
        block=config['block'],
        timeout=config['timeout']
    )

    try:
        conn1 = conn_pool.get_connection()
        print(f"conn1: {time.time()}")
        conn2 = conn_pool.get_connection()
        print(f"conn2: {time.time()}")
        conn3 = conn_pool.get_connection()
        print(f"conn3: {time.time()}")
    except Exception as e:
        print(f"exception: {time.time()}")
        print(e)

    return 0

if __name__ == '__main__':
    res = main()
    exit(res)

■実行結果
2つめのコネクションを取得し、5秒後にタイムアウトしています。

$ python test1.py
conn1: 1672927735.0839279
conn2: 1672927735.0845752
exception: 1672927740.0888727
Failed getting connection; pool exhausted

MySQL の blob に登録したデータをテキスト化

MySQL の blob に登録したデータをテキスト化する方法のメモ。
HTML のバイトデータを blob に保存し、エンコーディングを指定してテキスト化します。
■select

select convert(body using utf8) from test_base1 limit 1\G

■実行結果

*************************** 1. row ***************************
convert(body using utf8): <!DOCTYPE HTML>

<html lang="ja">
<head>
<meta charset="utf-8">
<meta name="referrer" content="unsafe-url">
<!--[if !IE]><meta http-equiv="X-UA-Compatible" content="IE=edge"><![endif]-->

<title>goo</title>
<meta name="csrf-token" content="9xtJ4WTRoHOPV1kb2ElCk5jA60yL8HGwX1v0Y0GP">
<meta name="keywords" content="goo,グー,ぐー,ポータル,portal,検索">
...

MySQL で主キーではないカラムでユニーク化

MySQL で主キーではないカラムでユニーク化する方法のメモ。
以下のテーブルで主キーではない id でユニーク化します。

create table test1 (
  id    varchar(16) not null,
  key1      varchar(16) not null,
  key2      varchar(16) not null,
  key3      varchar(16) not null,
  data      varchar(16) not null default '',

  primary key(key1, key2, key3),
  index(id)
);

insert into test1 set id = 'prd_01', key1 = 'a', key2 = 'a', key3 = '01', data = 'a';
insert into test1 set id = 'prd_01', key1 = 'a', key2 = 'a', key3 = '02', data = 'a';

insert into test1 set id = 'prd_02', key1 = 'b', key2 = 'b', key3 = '01', data = 'b';
insert into test1 set id = 'prd_02', key1 = 'b', key2 = 'b', key3 = '02', data = 'b';
insert into test1 set id = 'prd_02', key1 = 'b', key2 = 'b', key3 = '03', data = 'b';

insert into test1 set id = 'prd_03', key1 = 'c', key2 = 'c', key3 = '01', data = 'c';
insert into test1 set id = 'prd_03', key1 = 'c', key2 = 'c', key3 = '02', data = 'c';

以下では主キーの key1、key2、key3 を \t で連結し、文字列的に最小の値となるレコードでユニーク化します。

select
  t1.id as id
  , t1.data as data
from
  test1 t1
left join
(
  select
    id
    , min(concat(key1, '\t', key2, '\t', key3)) as k
  from
    test1
  group by
    id
) tk
on
  tk.k = concat(t1.key1, '_', t1.key2, '_', t1.key3)
where
  tk.k is not null
;

■実行結果

id      data
prd_01  a
prd_02  b
prd_03  c

MySQL の起動方法

MySQL の起動方法のメモ。

sudo systemctl start mysqld

jsonl のデータをオブジェクトとして MySQL に格納

jsonl 形式のデータをオブジェクトとして mysql に格納する方法のメモ。
以下の jsonl のデータを MySQL に格納します。
■登録データ

{"id": "01", "x": 30, "y": 170}
{"id": "02", "x": 20, "y": 180}
{"id": "03", "x": 25, "y": 160}
{"id": "04", "x": 40, "y": 175}
{"id": "05", "x": 45, "y": 180}

■MySQL のテーブル定義

create table test1 (
  obj  json,
  id   varchar(16) generated always as (json_unquote(obj->"$.id")) stored,
  x    integer generated always as (json_unquote(obj->"$.x")) stored,
  y    integer generated always as (json_unquote(obj->"$.y")) stored,

  primary key (id),
  index (x, y),
  index (y, x)
);

MySQL に登録する際に id を primary key とし、x、y にはインデックスを張ります。

■MySQL へのデータ登録
上記の jsonl 形式のデータを /var/lib/mysql-files/test1.jsonl に保存し、MySQL に load します。
このとき、fields terminated by '\t' を指定することで jsonl のデータが分割されないようにします。

load data infile '/var/lib/mysql-files/test1.jsonl' ignore into table test1 fields terminated by '\t' (obj);

■登録内容の確認

select * from test1;

obj     id      x       y
{"x": 30, "y": 170, "id": "01"} 01      30      170
{"x": 20, "y": 180, "id": "02"} 02      20      180
{"x": 25, "y": 160, "id": "03"} 03      25      160
{"x": 40, "y": 175, "id": "04"} 04      40      175
{"x": 45, "y": 180, "id": "05"} 05      45      180

■primary key の確認
以下の通り、id を指定した検索では PRIMARY KEY が利用されていることがわかります。

explain select id, x, y from test1 where id = '03'\G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test1
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 66
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

MySQL での json 型のデータへのインデックス

MySQL で json 型のデータにインデックスを張る方法のメモ。
テーブル定義でインデックスを張りたいデータ項目(x) に "generated always as (json_unquote(obj->"$.x")) stored" を指定します。
■テーブル定義

create table test1 (
  id   varchar(16) not null,
  obj  json,
  x    integer generated always as (json_unquote(obj->"$.x")) stored,
  y    integer generated always as (json_unquote(obj->"$.y")) stored,

  primary key (id),
  index (x, y),
  index (y, x)
);

■データ登録

insert into test1 set id = '01', obj = '{"id": "01", "x": 30, "y": 170}';
insert into test1 set id = '02', obj = '{"id": "02", "x": 20, "y": 180}';
insert into test1 set id = '03', obj = '{"id": "03", "x": 25, "y": 160}';
insert into test1 set id = '04', obj = '{"id": "04", "x": 40, "y": 175}';
insert into test1 set id = '05', obj = '{"id": "05", "x": 45, "y": 180}';

■検索

select id, x from test1 where x >= 30\G;

*************************** 1. row ***************************
id: 01
 x: 30
*************************** 2. row ***************************
id: 04
 x: 40
*************************** 3. row ***************************
id: 05
 x: 45

■実行計画
上記のクエリでは key に x が使われていることがわかります。

explain select id, x from test1 where x >= 30\G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test1
   partitions: NULL
         type: range
possible_keys: x,y
          key: x
      key_len: 5
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using where; Using index

MySQL の datetime でミリ秒、マイクロ秒の日時を登録

MySQL の datetime でミリ秒、マイクロ秒の日時を登録する方法のメモ。
datetime のカラムは秒まで、datetime(3) でミリ秒まで、datetime(6) でマイクロ秒までの精度となります。

■テーブル定義

create table dt1 (
  dt0       datetime,
  dt3       datetime(3),
  dt6       datetime(6),
  auto_dt0  datetime default current_timestamp,
  auto_dt3  datetime(3) default current_timestamp(3),
  auto_dt6  datetime(6) default current_timestamp(6)
);

■データ登録

insert into
  dt1
set
  dt0 = current_timestamp()
  , dt3 = current_timestamp(3)
  , dt6 = current_timestamp(6);

■検索

mysql> select * from dt1\G
*************************** 1. row ***************************
     dt0: 2022-06-22 07:45:42
     dt3: 2022-06-22 07:45:42.383
     dt6: 2022-06-22 07:45:42.383531
auto_dt0: 2022-06-22 07:45:42
auto_dt3: 2022-06-22 07:45:42.383
auto_dt6: 2022-06-22 07:45:42.383531