TiDB/MySQL小ネタ集
bohnen現在の接続がSSLを利用しているかどうかを確認する方法
SHOW STATUS LIKE "Ssl%";
| Variable_name | Value
| Ssl_cipher | TLS_AES_128_GCM_SHA256
| Ssl_cipher_list | RC4-SHA:DES-CBC3-SHA:AES128-SHA:AES256-SHA:AES128-SHA256:AES128-GCM-SHA256:AES256-GCM-SHA384:ECDHE-ECDSA-RC4-SHA:ECDHE-ECDSA-AES128-SHA:ECDHE-ECDSA-AES256-SHA:ECDHE-RSA-RC4-SHA:ECDHE-RSA-DES-CBC3-SHA:ECDHE-RSA-AES128-SHA:ECDHE-RSA-AES256-SHA:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES128-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-CHACHA20-POLY1305:ECDHE-ECDSA-CHACHA20-POLY1305:TLS_AES_128_GCM_SHA256:TLS_AES_256_GCM_SHA384:TLS_CHACHA20_POLY1305_SHA256: |
| Ssl_server_not_after | Nov 6 00:14:09 2024 UTC
| Ssl_server_not_before | Aug 8 00:14:09 2024 UTC
| Ssl_verify_mode | 5
| Ssl_version | TLSv1.3
collationの指定について
SET NAMESしても照合順序が反映されないと思ったら...
SET NAMES 'charset_name'ステートメントは、次のステートメントの組み合わせと同等です。
SET character_set_client = charset_name;
SET character_set_results = charset_name;
SET character_set_connection = charset_name;
ここで、character_set_clientはクライアントが送信するときの文字コード、character_set_resultレスポンスを返すときの文字コード、character_set_connection は接続で利用する文字コードで、character_set_client から変換されるとある。照合順序も同様に設定できる。
ただし、照合順序を例えば collation_connection で指定したとしても、リテラルの比較にしか使えないとある。テーブルやカラムにはcollationが設定してあり、そちらの指定の方が優先される。
特定のクエリでcollationを指定したいなら、collate句を使って設定するのが良い。
bohnen文字列の照合順の確認
strcmpで確認できる。また、実際の順序はweight_string で確認できる。
mysql> select strcmp(@a,@b collate utf8mb4_bin);
+-----------------------------------+
| strcmp(@a,@b collate utf8mb4_bin) |
+-----------------------------------+
| -1 |
+-----------------------------------+
mysql> select strcmp(@a,@b collate utf8mb4_0900_ai_ci);
+------------------------------------------+
| strcmp(@a,@b collate utf8mb4_0900_ai_ci) |
+------------------------------------------+
| 0 |
+------------------------------------------+
mysql> set @a = 'おっと' collate utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.00 sec)
mysql> set @b = 'おつと' collate utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT HEX(WEIGHT_STRING(@a)), HEX(WEIGHT_STRING(@b));
+------------------------+------------------------+
| HEX(WEIGHT_STRING(@a)) | HEX(WEIGHT_STRING(@b)) |
+------------------------+------------------------+
| 3D5F3D6C3D6E | 3D5F3D6C3D6E |
+------------------------+------------------------+
現在の文字コード、照合順
mysqlクライアントなら \s で確認できる。
mysql> \s
--------------
mysql Ver 8.3.0 for macos14.2 on arm64 (Homebrew)
...
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
SHOW VARIABLES like '%character%'やSHOW VARIABLES like '%collation%' でもよい。
mysql> show variables like '%character%';
+--------------------------+--------------------------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql-5.6.25-osx10.8-x86_64/share/charsets/ |
+--------------------------+--------------------------------------------------------+
mysql> show variables like '%collation%';
+---------------------------------------+--------------------+
| Variable_name | Value |
+---------------------------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database | utf8mb4_bin |
| collation_server | utf8mb4_bin |
| default_collation_for_utf8mb4 | utf8mb4_bin |
| tidb_hash_exchange_with_new_collation | ON |
+---------------------------------------+--------------------+
MySQLクライアントの--loaddata-infile オプション
LOAD DATA INFILE するときには、mysqlクライアントで --loaddata-infile オプションを付けておかないとセキュリティにより弾かれる。
内部的にLOAD DATAを使うTiDBのPlan Replayerのロードとかでも引っかかったのでメモ。
bohnenTiUP playgroundを外部から接続
デフォルトではTiUP Playgroundはlocalhost(127.0.0.1) で起動して、ローカルからのアクセスのみ受け付ける。外部からのアクセスも受け付けるようにするには、外部のIPを --host で指定する。
# 例
tiup playground --host 192.168.0.15
dockerで立ち上げたアプリケーションからローカルのTiUP Playgroundに接続するときなどに利用する。
bohnenデフォルト文字照合順の動き
良く使われる文字コードと照合順は Server, Database, Tableがあり、Table > Database > Server の順で参照される。何も設定されなかったら上位のレベルの照合順が参照されると思って良い。
ただし、文字コードや照合順の片方だけを指定した場合は、 SHOW CHARACTER SET で表示されるデフォルト文字コード・照合順の表を引き、対になる文字コード・照合順を設定する。
この表はMySQLとTiDBでは異なり、例えばMySQL 8.x では utf8mb4の照合順は utf8mb4_0900_ai_ci だが、TiDBでは utf8mb4_bin となっている。
文字コードのみをしてしているケースは意外と多く、そういうテーブル・データをTiDBに移行すると文字検索の挙動が異なる（半角・全角が区別されたりする）ので注意が必要。
bohnenテーブルを作成した後の照合順の変更
一度テーブル作成した後は、ALTER TABLE ... SET CHARACTER SET ... COLLATE ... 文では照合順の変更はできない。正確にはテーブルの照合順は変わるが、一度作成したカラムの照合順は変更できない。
これを変更するには、ALTER TABLE ... CONVERT TO CHARACTER SET ... COLLATE ... 文を使う。
ただし文字列カラムにインデクスが張ってあると変更ができない。
文字列がPKで、クラスターインデクスの場合(TiDBのデフォルト) インデクスが削除できないのでこの方式では変換できないことになる。
どうしてもだめな場合は、下記ステップで新しいテーブルを作成し、名前を変更する。（対象のテーブルをT1とする）
- T1の定義からCOLLATEを変更した新しいテーブルT2を作成する。
- T1のデータをT2へインポート。
INSERT INTO ... SELECT ...などを利用する。 -
RENAME TABLE ... TO ...で、テーブル名を入れ替える
1.と2.のステップを同時に実行できそうな CREATE TABLE ... SELECT ... という構文もある。試してはいない。