「竈門禰豆子」の「禰」が異体字セレクタによって7バイトあり、MySQLに適切な設定をしないと格納出来ない、というのがちょっと話題になったので試してみた。
Azure Database for PostgreSQL
まず、Azure Database for PostgreSQL Flexible Serverから。azコマンドでサクッと作成する。バージョン等一切指定せずリージョンのみAzure東日本に設定すると、PostgreSQL 12が立ち上がる。
% az postgres flexible-server create -l japaneast
Creating Resource Group 'group1876831446'...
Detected current client IP : 59.138.207.68
Do you want to enable access to client 59.138.207.68 (y/n) (y/n): y
Creating PostgreSQL Server 'server503548435' in group 'group1876831446'...
Your server 'server503548435' is using sku 'Standard_D2s_v3' (Paid Tier). Please refer to https://aka.ms/postgres-pricing for pricing details
Configuring server firewall rule to accept connections from '59.138.207.68'...
Creating PostgreSQL database 'flexibleserverdb'...
Make a note of your password. If you forget, you would have to reset your password with "az postgres flexible-server update -n server503548435 -g group1876831446 -p <new-password>".
Try using 'az postgres flexible-server connect' command to test out connection.
{
"connectionString": "postgresql://worriedshrimp8:bGp2Dgp4p2YNf2yV3kaSNA@server503548435.postgres.database.azure.com/postgres?sslmode=require",
"databaseName": "flexibleserverdb",
"firewallName": "FirewallIPAddress_2022-2-2_16-10-54",
"host": "server503548435.postgres.database.azure.com",
"id": "/subscriptions/050e5060-66b5-47ed-86fd-0429c9676a8c/resourceGroups/group1876831446/providers/Microsoft.DBforPostgreSQL/flexibleServers/server503548435",
"location": "Japan East",
"password": "bGp2Dgp4p2YNf2yV3kaSNA",
"resourceGroup": "group1876831446",
"skuname": "Standard_D2s_v3",
"username": "worriedshrimp8",
"version": "12"
}作成が終わったので、おもむろに接続してテスト。
% psql "postgresql://worriedshrimp8:bGp2Dgp4p2YNf2yV3kaSNA@server503548435.postgres.database.azure.com/postgres?sslmode=require"
psql (14.1, server 12.8)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=> create table test(test text);
CREATE TABLE
postgres=> insert into test(test) values('竈門禰豆子');
INSERT 0 1
postgres=> select * from test;
test
------------
竈門禰豆子
(1 row)特に問題は無さそう。
Azure Database for MySQL
次に、Azure Database for MySQL Flexible Server。こちらもazコマンドでサクッと作成する。バージョン等一切指定せずリージョンのみAzure東日本に設定すると、MySQL 5.7が立ち上がる。
% az mysql flexible-server create -l japaneast
Creating Resource Group 'group3286304388'...
Detected current client IP : 59.138.207.68
Do you want to enable access to client 59.138.207.68 (y/n) (y/n): y
IOPS is 396 which is either your input or free(maximum) IOPS supported for your storage size and SKU.
Creating MySQL Server 'server039039497' in group 'group3286304388'...
Your server 'server039039497' is using sku 'Standard_B1ms' (Paid Tier). Please refer to https://aka.ms/mysql-pricing for pricing details
Configuring server firewall rule to accept connections from '59.138.207.68'...
Creating MySQL database 'flexibleserverdb'...
Make a note of your password. If you forget, you would have to reset your password with'az mysql flexible-server update -n server039039497 -g group3286304388 -p <new-password>'.
Try using az 'mysql flexible-server connect' command to test out connection.
{
"connectionString": "mysql flexibleserverdb --host server039039497.mysql.database.azure.com --user scaredpeacock1 --password=d2yxrM_DCHTmzbhGIpmL_Q",
"databaseName": "flexibleserverdb",
"firewallName": "FirewallIPAddress_2022-2-2_16-20-15",
"host": "server039039497.mysql.database.azure.com",
"id": "/subscriptions/050e5060-66b5-47ed-86fd-0429c9676a8c/resourceGroups/group3286304388/providers/Microsoft.DBforMySQL/flexibleServers/server039039497",
"location": "Japan East",
"password": "d2yxrM_DCHTmzbhGIpmL_Q",
"resourceGroup": "group3286304388",
"skuname": "Standard_B1ms",
"username": "scaredpeacock1",
"version": "5.7"
}作成が終わったので、おもむろに接続してテスト。
% mysql flexibleserverdb --host server039039497.mysql.database.azure.com --user scaredpeacock1 --password=d2yxrM_DCHTmzbhGIpmL_Q
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.32-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use flexibleserverdb
Database changed
mysql> create table test (test text);
Query OK, 0 rows affected (0.38 sec)
mysql> insert into test (test) values('竈門禰豆子');
Query OK, 1 row affected (0.09 sec)
mysql> select * from test;
+-----------------+
| test |
+-----------------+
| 竈門禰豆子 |
+-----------------+
1 row in set (0.00 sec)こちらも特に問題は無さそう。
まとめ
クライアントの文字コードやらの詳細はおいておいて、デフォルトで作成されるPostgreSQL 12 / MySQL 5.7では問題が無さそう。Flexible ServerではPostgreSQL 13 / MySQL 8も作成出来るけれど、おそらく問題は無いだろうと思われ。