{"id":23110,"date":"2024-03-06T15:50:51","date_gmt":"2024-03-06T06:50:51","guid":{"rendered":"https:\/\/labs.gree.jp\/blog\/?p=23110"},"modified":"2024-03-06T15:50:21","modified_gmt":"2024-03-06T06:50:21","slug":"mysql-8-3-0-%e3%81%a7-character-set-client-handshake-%e3%81%8c%e5%89%8a%e9%99%a4%e3%81%95%e3%82%8c%e3%81%9f%e4%bb%b6%e3%81%ab%e3%81%a4%e3%81%84%e3%81%a6-%ef%bd%9e-go-sql-driver-mysql%e3%81%a8mysqlnd","status":"publish","type":"post","link":"https:\/\/labs.gree.jp\/blog\/2024\/03\/23110\/","title":{"rendered":"MySQL 8.3.0 \u3067 --character-set-client-handshake \u304c\u524a\u9664\u3055\u308c\u305f\u4ef6\u306b\u3064\u3044\u3066 \uff5e go-sql-driver\/mysql\u3068mysqlnd\u3092\u6dfb\u3048\u3066 \uff5e"},"content":{"rendered":"<p>\u3053\u3093\u306b\u3061\u308f\u3002\u305b\u3058\u307e\u3067\u3059\u3002<\/p>\n<p>\u76f8\u5909\u308f\u3089\u305aPHP\u3088\u304f\u308f\u304b\u3093\u306a\u3044\u3093\u3067\u3059\u304c\u3001\u4eca\u65e5\u3082PHP\u306e\u8a71\u3092\u3057\u307e\u3059\u3002\u3042\u3068\u3001go\u306e\u8a71\u3082\u3061\u3087\u3063\u3068\u3060\u3051\u3057\u307e\u3059\u3002\u66f8\u3044\u3066\u308b\u3046\u3061\u306b\u5c11\u3057\u9577\u6587\u306b\u306a\u3063\u3066\u3057\u307e\u3044\u307e\u3057\u305f\u304c\u3001\u5185\u5bb9\u3068\u3057\u3066\u306f\u3086\u308b\u3075\u308f\u3068\u8a00\u3048\u308b\u3093\u3058\u3083\u306a\u3044\u3067\u3057\u3087\u3046\u304b\u3002<\/p>\n<p>\u3067\u306f\u306f\u3058\u3081\u307e\u3059\u3002<\/p>\n<h2>\u306f\u3058\u3081\u306b<\/h2>\n<p>MySQL 8.2.0 \u306e release notes \u3067<\/p>\n<p><a href=\"https:\/\/dev.mysql.com\/doc\/relnotes\/mysql\/8.2\/en\/news-8-2-0.html#mysqld-8-2-0-deprecation-removal\">The --character-set-client-handshake server option, originally intended for use with upgrades from very old versions of MySQL, is now deprecated, and a warning is issued whenever it is used. You should expect this option to be removed in a future version of MySQL; applications depending on this option should begin migration away from it as soon as possible. (WL #13220)<\/a><\/p>\n<p>\u3068\u66f8\u304b\u308c\u3066\u3044\u305f --character-set-client-handshake \u304c\u30018.3.0 \u3067\u306f<\/p>\n<p><a href=\"https:\/\/dev.mysql.com\/doc\/relnotes\/mysql\/8.3\/en\/news-8-3-0.html#mysqld-8-3-0-deprecation-removal\">The --character-set-client-handshake and --old-style-user-limits server options were formerly used for compatibility with very old versions of MySQL which are no longer supported or maintained. Since they no longer serve any useful purpose, both options have been removed. (WL #13221, WL #13229)<\/a><\/p>\n<p>\u3068\u3044\u3046\u3053\u3068\u3067\u524a\u9664\u3055\u308c\u307e\u3057\u305f\u30028.2.0\u3067 deprecated \u3068\u544a\u77e5\u3055\u308c\u3066\u4e09\u30f6\u6708\u5f8c\u306e8.3.0\u3067\u524a\u9664\u3055\u308c\u305f\u306e\u3067\u3001\u78ba\u304b\u306b as soon as possible \u3067\u3059\u306d\u3002\u3055\u3059\u304c Innovation Release \u3067\u3059\u3002<\/p>\n<p>--character-set-client-handshake \u306f\u3001MySQL 8.0\u516c\u5f0f\u30c9\u30ad\u30e5\u30e1\u30f3\u30c8\u306e <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/faqs-cjk.html\">A.11 MySQL 8.0 FAQ: MySQL Chinese, Japanese, and Korean Character Sets<\/a> \u3067<\/p>\n<p><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/faqs-cjk.html#faq-cjk-how-use-4-0-charset\">However, some Asian customers prefer the MySQL 4.0 behavior. To make it possible to retain this behavior, we added a mysqld switch, --character-set-client-handshake, which can be turned off with --skip-character-set-client-handshake. If you start mysqld with --skip-character-set-client-handshake, then, when a client connects, it sends to the server the name of the character set that it wants to use. However, the server ignores this request from the client.<\/a><\/p>\n<p>\u3068\u8a18\u8ff0\u3055\u308c\u3066\u3044\u305f\u3082\u306e\u3067\u3059\u3002<br \/>\n\u30a2\u30b8\u30a2\u306e\u30e6\u30fc\u30b6\u3068\u3057\u3066\u306f\u3001 --skip-character-set-client-handshake \u304c\u306a\u304f\u306a\u308b\u3053\u3068\u306b\u3088\u3063\u3066\u3069\u306e\u3088\u3046\u306a\u5f71\u97ff\u304c\u51fa\u308b\u304b\u6c17\u306b\u306a\u308b\u3068\u3053\u308d\u306a\u306e\u3067\u3001\u516c\u5f0fmysql\u30af\u30e9\u30a4\u30a2\u30f3\u30c8\u3001Go\u3001PHP\u3067\u3069\u306e\u3088\u3046\u306a\u5f71\u97ff\u304c\u3067\u305d\u3046\u304b\u3001\u8efd\u304f\u8abf\u3079\u3066\u307f\u307e\u3057\u305f\u3002<\/p>\n<h2>\u4e88\u3081\u96d1\u306a\u307e\u3068\u3081<\/h2>\n<p>\u6700\u521d\u306b\u3056\u3063\u304f\u308a\u307e\u3068\u3081\u3066\u304a\u304d\u307e\u3059\u3002<\/p>\n<ul>\n<li>\u516c\u5f0f\u306emysql\u30af\u30e9\u30a4\u30a2\u30f3\u30c8\u3001<a href=\"https:\/\/github.com\/go-sql-driver\/mysql\">go-sql-driver\/mysql<\/a>\u3001PHP\u306emysqlnd\u3067\u3001 character set \u3084 collation \u306e\u30c7\u30d5\u30a9\u30eb\u30c8\u306e\u632f\u308b\u821e\u3044\u306f\u305d\u308c\u305e\u308c\u7570\u306a\u308b\u3002\n<ul>\n<li>\u516c\u5f0f\u306emysql\u30af\u30e9\u30a4\u30a2\u30f3\u30c8\u306flibmysqlclient\u306eMYSQL_AUTODETECT_CHARSET_NAME\u3092\u4f7f\u3063\u3066\u3044\u308b\u3002<\/li>\n<li>go-sql-driver\/mysql \u306f\u5185\u90e8\u3067\u30c7\u30d5\u30a9\u30eb\u30c8\u306ecollation\u3092\u5b9a\u7fa9\u3057\u3066\u3044\u308b\u3002 1.5.0 \u3067\u30c7\u30d5\u30a9\u30eb\u30c8\u306e collation \u304c utf8_general_ci \u304b\u3089 utf8mb4_general_ci \u306b\u306a\u3063\u305f\u3002<\/li>\n<li>PHP\u306emysqlnd\u306f\u30b5\u30fc\u30d0\u306egreet packet\u3067\u8fd4\u3063\u3066\u304d\u305fcharacter set\/collation\u3092\u30c7\u30d5\u30a9\u30eb\u30c8\u3067\u4f7f\u3046\u3002<\/li>\n<\/ul>\n<\/li>\n<li>PHP\u306emysqlnd\u306e\u30c7\u30d5\u30a9\u30eb\u30c8\u306f --skip-character-set-client-handshake \u306b\u8fd1\u3044\u632f\u308b\u821e\u3044\u3092\u3057\u3066\u3044\u308b\u3002--skip-character-set-client-handshake \u3092\u4f7f\u3063\u3066\u3044\u305fPHP\u30e6\u30fc\u30b6\u306f\u3001  --skip-character-set-client-handshake \u304c\u306a\u304f\u3066\u3082\u3001\u4eca\u307e\u3067\u3068\u8fd1\u3044\u611f\u899a\u3067\u6271\u3048\u308b\u3068\u601d\u308f\u308c\u308b\u3002<\/li>\n<li>PHP\u4ee5\u5916\u306e\u30d7\u30ed\u30b0\u30e9\u30df\u30f3\u30b0\u8a00\u8a9e\u3084\u30af\u30e9\u30a4\u30a2\u30f3\u30c8\u306f\u3001\u305d\u308c\u305e\u308c\u306e\u632f\u308b\u821e\u3044\u3092\u78ba\u8a8d\u3057\u305f\u4e0a\u3067\u3001\u5fc5\u8981\u306b\u5fdc\u3058\u3066\u660e\u793a\u7684\u306bcharacter set\u3084collation\u3092\u8a2d\u5b9a\u3057\u305f\u307b\u3046\u304c\u826f\u3044\u3002\n<ul>\n<li>character set \u3084 collation \u306e\u8a2d\u5b9a\u65b9\u6cd5\u306b\u3064\u3044\u3066\u306f\u3001\u5404\u30d7\u30ed\u30b0\u30e9\u30df\u30f3\u30b0\u8a00\u8a9e\u306e\u30af\u30e9\u30a4\u30a2\u30f3\u30c8\u30e9\u30a4\u30d6\u30e9\u30ea\u306e\u632f\u308b\u821e\u3044\u3092\u78ba\u8a8d\u3057\u305f\u307b\u3046\u304c\u826f\u3044\u3002\n<ul>\n<li>\u4f8b\u3048\u3070\u3001PHP\u3067\u306f \u300c<a href=\"https:\/\/www.php.net\/manual\/ja\/mysqli.set-charset.php\">mysqli_query() \u3067 (SET NAMES utf8 \u306a\u3069\u3068\u3057\u3066) \u8a2d\u5b9a\u3059\u308b\u65b9\u6cd5\u306f\u304a\u52e7\u3081\u3067\u304d\u307e\u305b\u3093\u3002<\/a>\u300d\u3068\u660e\u8a18\u3055\u308c\u3066\u3044\u308b\u3002<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<li>\u73fe\u5728\u3001 --skip-character-set-client-handshake \u3092\u6709\u52b9\u306b\u3057\u3066\u904b\u7528\u3057\u3066\u3044\u308b\u5834\u5408\u3001go-sql-driver\/mysql\u306e\u3088\u3046\u306butf8mb4_general_ci\u3092\u30c7\u30d5\u30a9\u30eb\u30c8\u306b\u3057\u3066\u63a5\u7d9a\u3057\u3066\u3044\u308b\u74b0\u5883\u3060\u3068\u3001\u5c06\u6765MySQL\u306e\u30d0\u30fc\u30b8\u30e7\u30f3\u3092\u4e0a\u3052\u305f\u3068\u304d\u3001\u4e88\u671f\u305b\u306ccollation\u304c\u4f7f\u308f\u308c\u308b\u53ef\u80fd\u6027\u304c\u3042\u308b\u3002\n<ul>\n<li>go-sql-driver\/mysql\u306e\u3088\u3046\u306b\u30af\u30e9\u30a4\u30a2\u30f3\u30c8\u5074\u3067utf8mb4_general_ci\u3092\u8a2d\u5b9a\u3057\u3066\u3044\u3066\u3082\u3001--skip-character-set-client-handshake \u304c\u8a2d\u5b9a\u3055\u308c\u3066\u3044\u308b\u3068\u3001 collation_server\u306e\u5024\u304c\u4f7f\u308f\u308c\u308b\u3002<\/li>\n<\/ul>\n<\/li>\n<li>init_connect \u3067\u3082\u306e\u3059\u3054\u3044\u9811\u5f35\u308b\u3068\u3001character set \u3084 collation \u306b\u3064\u3044\u3066\u3042\u308b\u7a0b\u5ea6\u878d\u901a\u306f\u52b9\u304f\u3002\u305f\u3060\u3057\u3001PHP\u306b\u304a\u3051\u308bSET NAMES\u306e\u3088\u3046\u306a\u554f\u984c\u3092\u5185\u5305\u3057\u3066\u3044\u308b\u53ef\u80fd\u6027\u306f\u3042\u308b\u3002<\/li>\n<\/ul>\n<p>\u3067\u306f\u8a73\u7d30\u306b\u79fb\u308a\u307e\u3059\u3002<\/p>\n<h2>\u516c\u5f0f\u306e mysql \u30af\u30e9\u30a4\u30a2\u30f3\u30c8\u306e\u632f\u308b\u821e\u3044\uff08libmysqlclient\u306eMYSQL_AUTODETECT_CHARSET_NAME\u306e\u632f\u308b\u821e\u3044\uff09<\/h2>\n<p><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/ja\/linux-installation-apt-repo.html\">\u516c\u5f0f\u306eMySQL\u306eapt\u30ea\u30dd\u30b8\u30c8\u30ea\u304b\u3089 sudo apt-get install mysql-client \u3059\u308b<\/a>\u3068<\/p>\n<pre><code>$ mysql --version\nmysql  Ver 8.0.36 for Linux on x86_64 (MySQL Community Server - GPL)\n$ mysql --help | grep ^default-character-set\ndefault-character-set                     auto\n$\n<\/code><\/pre>\n<p>default-character-set=auto \u306b\u306a\u3063\u3066\u3044\u3066\u3001\u3053\u306e auto \u3063\u3066\u306a\u3093\u305e\uff1f\u3063\u3066\u8a71\u3067\u3059\u304c<\/p>\n<p><a href=\"https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.36\/client\/mysql.cc#L189\">https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.36\/client\/mysql.cc#L189<\/a><br \/>\n<a href=\"https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.36\/include\/mysql_com.h#L71\">https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.36\/include\/mysql_com.h#L71<\/a><\/p>\n<p><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/ja\/charset-connection.html#charset-connection-client-configuration\">\u516c\u5f0f\u30c9\u30ad\u30e5\u30e1\u30f3\u30c8\u306b\u3042\u308bMYSQL_AUTODETECT_CHARSET_NAME<\/a>\u3067\u3059\u3002\u516c\u5f0f\u30c9\u30ad\u30e5\u30e1\u30f3\u30c8\u66f0\u304f<\/p>\n<blockquote><p>\n  \u5404\u30af\u30e9\u30a4\u30a2\u30f3\u30c8\u306f\u3001Unix \u30b7\u30b9\u30c6\u30e0\u306e LANG \u307e\u305f\u306f LC_ALL \u30ed\u30b1\u30fc\u30eb\u74b0\u5883\u5909\u6570\u306e\u5024\u3084 Windows \u30b7\u30b9\u30c6\u30e0\u306e\u30b3\u30fc\u30c9\u30da\u30fc\u30b8\u8a2d\u5b9a\u306a\u3069\u3001\u30aa\u30da\u30ec\u30fc\u30c6\u30a3\u30f3\u30b0\u30b7\u30b9\u30c6\u30e0\u306e\u8a2d\u5b9a\u306b\u57fa\u3065\u3044\u3066\u3001\u4f7f\u7528\u3059\u308b\u6587\u5b57\u30bb\u30c3\u30c8\u3092\u81ea\u52d5\u691c\u51fa\u3067\u304d\u307e\u3059\u3002 \u30ed\u30b1\u30fc\u30eb\u304c OS \u304b\u3089\u5229\u7528\u3067\u304d\u308b\u30b7\u30b9\u30c6\u30e0\u306e\u5834\u5408\u3001\u30af\u30e9\u30a4\u30a2\u30f3\u30c8\u306f\u30b3\u30f3\u30d1\u30a4\u30eb\u6642\u306e\u30c7\u30d5\u30a9\u30eb\u30c8\u3092\u4f7f\u7528\u3059\u308b\u306e\u3067\u306f\u306a\u304f\u3001\u3053\u306e\u30ed\u30b1\u30fc\u30eb\u3092\u4f7f\u7528\u3057\u3066\u30c7\u30d5\u30a9\u30eb\u30c8\u306e\u6587\u5b57\u30bb\u30c3\u30c8\u3092\u8a2d\u5b9a\u3057\u307e\u3059\u3002\n<\/p><\/blockquote>\n<p>\u3068\u3044\u3046\u3084\u3064\u3067\u3059\u306d\u3002Ubuntu 22.04 LTS on WSL2\u3068docker\u3067\u8a66\u3057\u3066\u307f\u307e\u3057\u3087\u3046\u3002<\/p>\n<pre><code>$ sudo apt install language-pack-ja\n$ sudo locale-gen ja_JP.EUC-JP\n<\/code><\/pre>\n<p>\u3057\u305f\u5f8c\u3001LANG\u3092\u5207\u308a\u66ff\u3048\u305f\u308a\u3057\u306a\u304c\u3089mysql\u30af\u30e9\u30a4\u30a2\u30f3\u30c8\u3067\u63a5\u7d9a\u3057\u3066\u307f\u307e\u3059\u3002<\/p>\n<pre><code>$ docker run -p 127.0.0.1:13306:3306 -e MYSQL_ROOT_HOST=172.17.0.1 -e MYSQL_ROOT_PASSWORD=sejima --name mysql8036 -d container-registry.oracle.com\/mysql\/community-server:8.0.36\n244e458b9670f04059684bd710724a1d71430e2f2d0d8fbd26c549dcf11ea3c1\n$ MYSQL_PWD=sejima mysql -u root -e \"show variables like '%character%'\" -h 127.0.0.1 -P 13306\n+--------------------------+--------------------------------+\n| Variable_name            | Value                          |\n+--------------------------+--------------------------------+\n| character_set_client     | utf8mb4                        |\n| character_set_connection | utf8mb4                        |\n| character_set_database   | utf8mb4                        |\n| character_set_filesystem | binary                         |\n| character_set_results    | utf8mb4                        |\n| character_set_server     | utf8mb4                        |\n| character_set_system     | utf8mb3                        |\n| character_sets_dir       | \/usr\/share\/mysql-8.0\/charsets\/ |\n+--------------------------+--------------------------------+\n$ LANG=ja_JP.eucjp MYSQL_PWD=sejima mysql -u root -e \"show variables like '%character%'\" -h 127.0.0.1 -P 13306\n+--------------------------+--------------------------------+\n| Variable_name            | Value                          |\n+--------------------------+--------------------------------+\n| character_set_client     | ujis                           |\n| character_set_connection | ujis                           |\n| character_set_database   | utf8mb4                        |\n| character_set_filesystem | binary                         |\n| character_set_results    | ujis                           |\n| character_set_server     | utf8mb4                        |\n| character_set_system     | utf8mb3                        |\n| character_sets_dir       | \/usr\/share\/mysql-8.0\/charsets\/ |\n+--------------------------+--------------------------------+\n$\n<\/code><\/pre>\n<p>LANG=ja_JP.eucjp\u306b\u3059\u308b\u3068\u3001character_set_client\u3001character_set_connection\u3001character_set_results\u304cujis\u306b\u306a\u308a\u307e\u3057\u305f\u3002<br \/>\n\u306a\u304a\u3001LANG=ja_JP.eucjp\u3067\u3042\u3063\u3066\u3082\u3001--default-character-set\u3084--init-command\u3067\u4e0a\u66f8\u304d\u53ef\u80fd\u3067\u3059\u3002<\/p>\n<pre><code>$ LANG=ja_JP.eucjp MYSQL_PWD=sejima mysql -u root -e \"show variables like '%character%'\" -h 127.0.0.1 -P 13306 --default-character-set=utf8mb4\n+--------------------------+--------------------------------+\n| Variable_name            | Value                          |\n+--------------------------+--------------------------------+\n| character_set_client     | utf8mb4                        |\n| character_set_connection | utf8mb4                        |\n| character_set_database   | utf8mb4                        |\n| character_set_filesystem | binary                         |\n| character_set_results    | utf8mb4                        |\n| character_set_server     | utf8mb4                        |\n| character_set_system     | utf8mb3                        |\n| character_sets_dir       | \/usr\/share\/mysql-8.0\/charsets\/ |\n+--------------------------+--------------------------------+\n$ LANG=ja_JP.eucjp MYSQL_PWD=sejima mysql -u root -e \"show variables like '%character%'\" -h 127.0.0.1 -P 13306 --init-command='SET NAMES utf8mb4'\n+--------------------------+--------------------------------+\n| Variable_name            | Value                          |\n+--------------------------+--------------------------------+\n| character_set_client     | utf8mb4                        |\n| character_set_connection | utf8mb4                        |\n| character_set_database   | utf8mb4                        |\n| character_set_filesystem | binary                         |\n| character_set_results    | utf8mb4                        |\n| character_set_server     | utf8mb4                        |\n| character_set_system     | utf8mb3                        |\n| character_sets_dir       | \/usr\/share\/mysql-8.0\/charsets\/ |\n+--------------------------+--------------------------------+\n$\n<\/code><\/pre>\n<p>\u3053\u306e\u3088\u3046\u306b\u3001mysql\u30af\u30e9\u30a4\u30a2\u30f3\u30c8\u3067\u8a2d\u5b9a\u3055\u308c\u305fcharacter set\u3092mysqld\u306fcharacter_set_client\u306b\u8a2d\u5b9a\u3059\u308b\u308f\u3051\u3067\u3059\u304c\u3001--character-set-client-handshake=off(--skip-character-set-client-handshake)\u306b\u3059\u308b\u3053\u3068\u306b\u3088\u3063\u3066\u3001character_set_server\u306e\u5024\u3067\u5f37\u5236\u3059\u308b\u3053\u3068\u304c\u3067\u304d\u307e\u3059\u3002\u5177\u4f53\u7684\u306b\u306f\u3001\u6b21\u306e\u3088\u3046\u306b\u306a\u308a\u307e\u3059\u3002<\/p>\n<pre><code>$ docker run -p 127.0.0.1:13306:3306 -e MYSQL_ROOT_HOST=172.17.0.1 -e MYSQL_ROOT_PASSWORD=sejima --name mysql8036 -d container-registry.oracle.com\/mysql\/community-server:8.0.36 --character-set-client-handshake=off\nf589a9b2472fc2c4638f13c182a1446a5e6ca1db75963941f53e9faaa16d5d00\n$ LANG=ja_JP.eucjp MYSQL_PWD=sejima mysql -u root -e \"show variables like '%character%'\" -h 127.0.0.1 -P 13306\n+--------------------------+--------------------------------+\n| Variable_name            | Value                          |\n+--------------------------+--------------------------------+\n| character_set_client     | utf8mb4                        |\n| character_set_connection | utf8mb4                        |\n| character_set_database   | utf8mb4                        |\n| character_set_filesystem | binary                         |\n| character_set_results    | utf8mb4                        |\n| character_set_server     | utf8mb4                        |\n| character_set_system     | utf8mb3                        |\n| character_sets_dir       | \/usr\/share\/mysql-8.0\/charsets\/ |\n+--------------------------+--------------------------------+\n$\n<\/code><\/pre>\n<h2>go-sql-driver\/mysql \u306e\u632f\u308b\u821e\u3044<\/h2>\n<p>go-sql-driver\/mysql \u3067\u306f\u30c7\u30d5\u30a9\u30eb\u30c8\u306echaracter set\uff08\u6b63\u78ba\u306b\u306fcollation\uff09\u304c\u5b9a\u7fa9\u3055\u308c\u3066\u304a\u308a\u3001<a href=\"https:\/\/github.com\/go-sql-driver\/mysql\/blob\/v1.5.0\/CHANGELOG.md\">1.5 (2020-01-07) \u3067\u30c7\u30d5\u30a9\u30eb\u30c8\u304c utf8_general_ci \u304b\u3089 utf8mb4_general_ci \u306b\u306a\u308a\u307e\u3057\u305f\u3002<\/a><\/p>\n<p><a href=\"https:\/\/github.com\/go-sql-driver\/mysql\/pull\/877\">Update collations and make utf8mb4 default #877<\/a><br \/>\n<a href=\"https:\/\/github.com\/go-sql-driver\/mysql\/pull\/1054\">README: update default collation to utf8mb4_general_ci #1054<\/a><\/p>\n<p>\u96d1\u306b\u691c\u8a3c\u7528\u306e\u30b3\u30fc\u30c9\u3092\u66f8\u3044\u3066\u78ba\u8a8d\u3057\u3066\u307f\u307e\u3059\u3002\u6b21\u306e\u30b3\u30fc\u30c9\u306f\u3001go 1.18.1\u3001github.com\/go-sql-driver\/mysql v1.7.1\u3067\u78ba\u8a8d\u3057\u307e\u3057\u305f\u3002<\/p>\n<pre class=\"lang:go\"><code>package main\n\nimport (\n    \"database\/sql\"\n    \"fmt\"\n\n    _ \"github.com\/go-sql-driver\/mysql\"\n)\n\nfunc main() {\n    db, err := sql.Open(\"mysql\", \"root:sejima@(127.0.0.1:13306)\/\")\n    if err != nil {\n        panic(err.Error())\n    }\n    defer db.Close()\n\n    rows, err := db.Query(\"SHOW VARIABLES LIKE 'character_set_%'\")\n\n    if err != nil {\n        panic(err.Error())\n    }\n\n    var col1, col2 []byte\n\n    for rows.Next() {\n        err = rows.Scan(&col1, &col2)\n\n        if err != nil {\n            panic(err.Error())\n        }\n\n        fmt.Printf(\"%s (%s)\\n\", col1, col2)\n    }\n\n    if err = rows.Err(); err != nil {\n        panic(err.Error())\n    }\n\n    rows, err = db.Query(\"SHOW VARIABLES LIKE 'collation_%'\")\n\n    if err != nil {\n        panic(err.Error())\n    }\n\n    for rows.Next() {\n        err = rows.Scan(&col1, &col2)\n\n        if err != nil {\n            panic(err.Error())\n        }\n\n        fmt.Printf(\"%s (%s)\\n\", col1, col2)\n    }\n    if err = rows.Err(); err != nil {\n        panic(err.Error())\n    }\n}<\/code><\/pre>\n<p>go-sql-driver\/mysql \u3082 MySQL 8.0 \u3082\u30c7\u30d5\u30a9\u30eb\u30c8\u306e\u307e\u307e\u3060\u3068\u3001collation_connection\u306futf8mb4_general_ci\u3001collation_server\u306futf8mb4_0900_ai_ci\u306b\u306a\u308a\u307e\u3059\u3002\u3044\u305a\u308c\u3082 character set \u3068\u3057\u3066\u306futf8mb4\u3067\u3059\u304c\u3001<a href=\"https:\/\/mita2db.hateblo.jp\/entry\/2020\/12\/07\/000000\">utf8mb4_general_ci\u3068utf8mb4_0900_ai_ci\u306f\u632f\u308b\u821e\u3044\u304c\u5927\u304d\u304f\u7570\u306a\u308b<\/a>\u306e\u3067\u3001Go\u3067utf8mb4\u3092\u4f7f\u3046\u306a\u3089\u3001\u610f\u56f3\u3057\u305fcollation\u304c\u9069\u7528\u3055\u308c\u3066\u3044\u308b\u304b\u6ce8\u610f\u304c\u5fc5\u8981\u3067\u3059\u3002<\/p>\n<pre><code>$ docker run -p 127.0.0.1:13306:3306 -e MYSQL_ROOT_HOST=172.17.0.1 -e MYSQL_ROOT_PASSWORD=sejima --name mysql8036 -d container-registry.oracle.com\/mysql\/community-server:8.0.36\nf7bbeda9c4ca8c4b0b46173c05d6499b015f7c821e94d21e081563b8a4bb2ded\n$ go run test.go\ncharacter_set_client (utf8mb4)\ncharacter_set_connection (utf8mb4)\ncharacter_set_database (utf8mb4)\ncharacter_set_filesystem (binary)\ncharacter_set_results (utf8mb4)\ncharacter_set_server (utf8mb4)\ncharacter_set_system (utf8mb3)\ncharacter_sets_dir (\/usr\/share\/mysql-8.0\/charsets\/)\ncollation_connection (utf8mb4_general_ci)\ncollation_database (utf8mb4_0900_ai_ci)\ncollation_server (utf8mb4_0900_ai_ci)\n$ \n<\/code><\/pre>\n<p>--character-set-server=ujis \u306e\u3068\u304d\u3001go-sql-driver\/mysql\u306f\u30c7\u30d5\u30a9\u30eb\u30c8\u306e\u307e\u307e\u3060\u3068\u3001character_set_client \u306f utf8mb4 \u306b\u306a\u308a\u307e\u3059\u3002<\/p>\n<pre><code>$ docker run -p 127.0.0.1:13306:3306 -e MYSQL_ROOT_HOST=172.17.0.1 -e MYSQL_ROOT_PASSWORD=sejima --name mysql8036 -d container-registry.oracle.com\/mysql\/community-server:8.0.36 --character-set-server=ujis\n75ee78d977d12fbef29a0b2366d288e2d5e074cc329ca53660f913c6e26a809d\n$ go run test.go\ncharacter_set_client (utf8mb4)\ncharacter_set_connection (utf8mb4)\ncharacter_set_database (ujis)\ncharacter_set_filesystem (binary)\ncharacter_set_results (utf8mb4)\ncharacter_set_server (ujis)\ncharacter_set_system (utf8mb3)\ncharacter_sets_dir (\/usr\/share\/mysql-8.0\/charsets\/)\ncollation_connection (utf8mb4_general_ci)\ncollation_database (ujis_japanese_ci)\ncollation_server (ujis_japanese_ci)\n$\n<\/code><\/pre>\n<p>--character-set-client-handshake=off \u306b\u3059\u308b\u3068\u3001collation_connection\u3082collation_server\u3068\u540c\u69d8\u306butf8mb4_0900_ai_ci\u306b\u306a\u308a\u307e\u3059\u3002<\/p>\n<pre><code>$ docker run -p 127.0.0.1:13306:3306 -e MYSQL_ROOT_HOST=172.17.0.1 -e MYSQL_ROOT_PASSWORD=sejima --name mysql8036 -d container-registry.oracle.com\/mysql\/community-server:8.0.36 --character-set-client-handshake=off\nea94ccbe5cc91c90fa3e768e260cb436bf8b2989f0629e84623e4afa0fef3df8\n$ go run test.go\ncharacter_set_client (utf8mb4)\ncharacter_set_connection (utf8mb4)\ncharacter_set_database (utf8mb4)\ncharacter_set_filesystem (binary)\ncharacter_set_results (utf8mb4)\ncharacter_set_server (utf8mb4)\ncharacter_set_system (utf8mb3)\ncharacter_sets_dir (\/usr\/share\/mysql-8.0\/charsets\/)\ncollation_connection (utf8mb4_0900_ai_ci)\ncollation_database (utf8mb4_0900_ai_ci)\ncollation_server (utf8mb4_0900_ai_ci)\n$\n<\/code><\/pre>\n<p>\u307e\u305f\u3001 --character-set-server=ujis \u304b\u3064 --character-set-client-handshake=off \u306b\u3059\u308b\u3068\u3001character_set_client\u306fujis\u306b\u306a\u308a\u307e\u3059\u3002<\/p>\n<pre><code>$ docker run -p 127.0.0.1:13306:3306 -e MYSQL_ROOT_HOST=172.17.0.1 -e MYSQL_ROOT_PASSWORD=sejima --name mysql8036 -d container-registry.oracle.com\/mysql\/community-server:8.0.36 --character-set-client-handshake=off --character-set-server=ujis\n5e0a1b5ca885c258fc1ade3d5e716cab54cce94ebaeba2bb07bc3e48d9cb5c9a\n$ go run test.go\ncharacter_set_client (ujis)\ncharacter_set_connection (ujis)\ncharacter_set_database (ujis)\ncharacter_set_filesystem (binary)\ncharacter_set_results (ujis)\ncharacter_set_server (ujis)\ncharacter_set_system (utf8mb3)\ncharacter_sets_dir (\/usr\/share\/mysql-8.0\/charsets\/)\ncollation_connection (ujis_japanese_ci)\ncollation_database (ujis_japanese_ci)\ncollation_server (ujis_japanese_ci)\n$\n<\/code><\/pre>\n<p>go-sql-driver\/mysql \u306e \u30c7\u30d5\u30a9\u30eb\u30c8\u306e collation \u304c MySQL 8.0 \u306e\u30c7\u30d5\u30a9\u30eb\u30c8\u3068\u7570\u306a\u308b\u3068\u3044\u3046\u306e\u306f\u8981\u6ce8\u610f\u3067\u3059\u304c\u3001 go-sql-driver\/mysql \u306e\u632f\u308b\u821e\u3044\u306f\u6bd4\u8f03\u7684\u7d20\u76f4\u306a\u3082\u306e\u306b\u898b\u3048\u307e\u3059\u3002<\/p>\n<h2>PHP\u306emysqlnd<\/h2>\n<p>PHP\u3088\u304f\u308f\u304b\u3089\u306a\u3044\u306e\u3067\u3001\u307e\u305a\u306f <a href=\"https:\/\/www.php.net\/downloads.php\">https:\/\/www.php.net\/downloads.php<\/a> \u304b\u3089 8.1.27 \u306e\u30bd\u30fc\u30b9\u30b3\u30fc\u30c9\u3092\u30c0\u30a6\u30f3\u30ed\u30fc\u30c9\u3057\u3066\u30c7\u30d0\u30c3\u30b0\u30d3\u30eb\u30c9\u3057\u307e\u3059\u3002<\/p>\n<pre><code>$ tar xf php-8.1.27.tar.xz\n$ cd php-8.1.27\/\n$ .\/configure --enable-debug --with-mysqli=mysqlnd --with-openssl\n$ make -j\n$ cd sapi\/cli\/\n<\/code><\/pre>\n<p>\u30c7\u30d0\u30c3\u30b0\u30d3\u30eb\u30c9\u3059\u308b\u3053\u3068\u3067 <a href=\"https:\/\/www.php.net\/manual\/ja\/mysqli.debug.php\">mysqli_debug<\/a> \u304c\u4f7f\u3048\u308b\u3088\u3046\u306b\u306a\u308b\u306e\u3067\u3001\u6b21\u306e\u3088\u3046\u306a\u691c\u8a3c\u7528\u306e\u30b9\u30af\u30ea\u30d7\u30c8\u3092\u66f8\u3044\u3066\u307f\u307e\u3059\u3002<\/p>\n<pre class=\"lang:php\"><code><?php\n\nmysqli_debug(\"T:n:t:m:x:F:L:o,\/tmp\/client.trace\");\n\nprintf(\"Client library version: %s\\n\", mysqli_get_client_info());\n\n$mysqli = new mysqli('127.0.0.1', 'root', 'sejima', null, 13306);\nif ($mysqli->connect_errno) {\n   echo \"Errno: \" . $mysqli->connect_errno . PHP_EOL;\n   echo \"Error: \" . $mysqli->connect_error . PHP_EOL;\n   exit;\n}\n\necho \"PHP\" . phpversion() . PHP_EOL;\n\n$sql = \"SHOW VARIABLES LIKE 'character_set_%'\";\nif (!$result = $mysqli->query($sql)) {\n   echo \"Query: \" . $sql . PHP_EOL;\n   echo \"Errno: \" . $mysqli->errno . PHP_EOL;\n   echo \"Error: \" . $mysqli->error . PHP_EOL;\n   exit;\n}\n\n$rows = $result->fetch_all(MYSQLI_NUM);\nforeach ($rows as $row) {\n    printf(\"%s (%s)\\n\", $row[0], $row[1]);\n}\n\n$result->free();\n\n$sql = \"SHOW VARIABLES LIKE 'collation_%'\";\nif (!$result = $mysqli->query($sql)) {\n   echo \"Query: \" . $sql . PHP_EOL;\n   echo \"Errno: \" . $mysqli->errno . PHP_EOL;\n   echo \"Error: \" . $mysqli->error . PHP_EOL;\n   exit;\n}\n\n$rows = $result->fetch_all(MYSQLI_NUM);\nforeach ($rows as $row) {\n    printf(\"%s (%s)\\n\", $row[0], $row[1]);\n}\n\n$result->free();\n$mysqli->close();\n?><\/code><\/pre>\n<p>--character-set-client-handshake \u306e\u8a2d\u5b9a\u3092\u5909\u3048\u305a\u306b MySQL 8.0 \u306e\u30c7\u30d5\u30a9\u30eb\u30c8\u306e\u8a2d\u5b9a\u3067\u7e4b\u3044\u3067\u307f\u308b\u3068\u3001 mysqlnd \u3067\u306f collation_connection \u306f utf8mb4_0900_ai_ci \u306b\u306a\u308a\u307e\u3057\u305f\u3002<\/p>\n<pre><code>$ docker run -p 127.0.0.1:13306:3306 -e MYSQL_ROOT_HOST=172.17.0.1 -e MYSQL_ROOT_PASSWORD=sejima --name mysql8036 -d container-registry.oracle.com\/mysql\/community-server:8.0.36\n1956580bf6adea2cda020ad71e67674417ad27118c4b2ff905c1f5c59c3ab2b0\n$ .\/php ~\/test.php\nClient library version: mysqlnd 8.1.27\nPHP8.1.27\ncharacter_set_client (utf8mb4)\ncharacter_set_connection (utf8mb4)\ncharacter_set_database (utf8mb4)\ncharacter_set_filesystem (binary)\ncharacter_set_results (utf8mb4)\ncharacter_set_server (utf8mb4)\ncharacter_set_system (utf8mb3)\ncharacter_sets_dir (\/usr\/share\/mysql-8.0\/charsets\/)\ncollation_connection (utf8mb4_0900_ai_ci)\ncollation_database (utf8mb4_0900_ai_ci)\ncollation_server (utf8mb4_0900_ai_ci)\n$\n<\/code><\/pre>\n<p>\u6b21\u306b\u3001 --character-set-server=ujis \u3067\u8d77\u52d5\u3057\u3066\u307f\u308b\u3068\u3001\u4eca\u5ea6\u306fcharacter_set_client\u304cujis\u306b\u306a\u308a\u307e\u3057\u305f\u3002<\/p>\n<pre><code>$ docker run -p 127.0.0.1:13306:3306 -e MYSQL_ROOT_HOST=172.17.0.1 -e MYSQL_ROOT_PASSWORD=sejima --name mysql8036 -d container-registry.oracle.com\/mysql\/community-server:8.0.36 --character-set-server=ujis\n7966361f49e0c7a20aa22351a3353d520a0a005d9c338b1cce59173e1faaad60\n$ .\/php ~\/test.php\nClient library version: mysqlnd 8.1.27\nPHP8.1.27\ncharacter_set_client (ujis)\ncharacter_set_connection (ujis)\ncharacter_set_database (ujis)\ncharacter_set_filesystem (binary)\ncharacter_set_results (ujis)\ncharacter_set_server (ujis)\ncharacter_set_system (utf8mb3)\ncharacter_sets_dir (\/usr\/share\/mysql-8.0\/charsets\/)\ncollation_connection (ujis_japanese_ci)\ncollation_database (ujis_japanese_ci)\ncollation_server (ujis_japanese_ci)\n$\n<\/code><\/pre>\n<p>\u306a\u3093\u3067\u3084\u306d\u3093\uff1f<\/p>\n<p>\u305d\u3046\u304a\u601d\u3044\u306e\u65b9\u3082\u3044\u3089\u3063\u3057\u3083\u308b\u3053\u3068\u3067\u3057\u3087\u3046\u3002\u79c1\u306f\u601d\u3044\u307e\u3057\u305f\u3002\u3053\u308c\u304cPHP\u3067\u3059\u3002<\/p>\n<p><a href=\"https:\/\/www.php.net\/manual\/en\/mysqli.options.php\">mysqli_options<\/a>\u306e\u30c9\u30ad\u30e5\u30e1\u30f3\u30c8\u306b\u306f\u6b21\u306e\u3088\u3046\u306b\u66f8\u304b\u308c\u3066\u3044\u307e\u3059\u3002<\/p>\n<blockquote><p>\n  MySQLnd always assumes the server default charset. This charset is sent during connection hand-shake\/authentication, which mysqlnd will use.\n<\/p><\/blockquote>\n<p>\u3053\u3053\u3067\u3001\u30c7\u30d0\u30c3\u30b0\u30d3\u30eb\u30c9\u3059\u308b\u3053\u3068\u3067\u6709\u52b9\u306b\u3067\u304d\u305fmysqli_debug\u304c\u6d3b\u304d\u3066\u304d\u307e\u3059\u3002\u51fa\u529b\u3057\u305f\u30ed\u30b0\u3092\u898b\u308b\u3068\u3001<a href=\"https:\/\/github.com\/php\/php-src\/blob\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_wireprotocol.c#L324-L402\">php_mysqlnd_greet_read()<\/a>\u3067<a href=\"https:\/\/github.com\/php\/php-src\/blob\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_wireprotocol.c#L402-L457\">charset_no\u3092\u8aad\u3093\u3067\u3044\u308b<\/a>\u3053\u3068\u304c\u308f\u304b\u308a\u307e\u3059\u3002<\/p>\n<p>mysqld\u306e\u8a2d\u5b9a\u304c\u30c7\u30d5\u30a9\u30eb\u30c8\u306e\u3068\u304d\u306f charset_no=255 \u304c<\/p>\n<pre><code>$ sed -n '\/>.*greet_read.*\/,\/<.*greet_read.*\/p' \/tmp\/client.trace\n14:40:26.300264 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_wireprotocol.c:   33   4:| | | | >php_mysqlnd_greet_read\n14:40:26.300290 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_wireprotocol.c:   29   5:| | | | | >mysqlnd_read_packet_header_and_body\n14:40:26.300292 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_wireprotocol.c:   29   6:| | | | | | info : buf=0x7ffed16badd0 size=2048\n14:40:26.300294 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_wireprotocol.c:   24   6:| | | | | | >mysqlnd_read_header\n14:40:26.300296 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_wireprotocol.c:   24   7:| | | | | | | info : compressed=0\n14:40:26.300299 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_protocol_frame_codec.c:   32   7:| | | | | | | >mysqlnd_pfc::receive\n14:40:26.300301 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_protocol_frame_codec.c:   37   7:| | | | | | | <mysqlnd_pfc::receive (total=1 own=1 in_calls=0)\n14:40:26.300303 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_vio.c:   71   7:| | | | | | | >mysqlnd_vio::get_stream\n14:40:26.300305 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_vio.c:   71   8:| | | | | | | | info : 0x7f87d5a83600\n14:40:26.300307 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_vio.c:   71   7:| | | | | | | <mysqlnd_vio::get_stream (total=2 own=2 in_calls=0)\n14:40:26.300309 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_vio.c:    8   7:| | | | | | | >mysqlnd_vio::network_read\n14:40:26.300311 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_vio.c:    8   8:| | | | | | | | info : count=4\n14:40:26.300980 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_vio.c:    9   7:| | | | | | | <mysqlnd_vio::network_read (total=668 own=668 in_calls=0)\n14:40:26.300994 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_wireprotocol.c:   25   7:| | | | | | | info : HEADER: prot_packet_no=0 size= 74\n14:40:26.300998 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_wireprotocol.c:   26   6:| | | | | | <mysqlnd_read_header (total=702 own=31 in_calls=671)\n14:40:26.301001 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_protocol_frame_codec.c:   32   6:| | | | | | >mysqlnd_pfc::receive\n14:40:26.301003 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_protocol_frame_codec.c:   37   6:| | | | | | <mysqlnd_pfc::receive (total=0 own=0 in_calls=0)\n14:40:26.301006 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_vio.c:   71   6:| | | | | | >mysqlnd_vio::get_stream\n14:40:26.301008 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_vio.c:   71   7:| | | | | | | info : 0x7f87d5a83600\n14:40:26.301010 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_vio.c:   71   6:| | | | | | <mysqlnd_vio::get_stream (total=2 own=2 in_calls=0)\n14:40:26.301014 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_vio.c:    8   6:| | | | | | >mysqlnd_vio::network_read\n14:40:26.301016 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_vio.c:    8   7:| | | | | | | info : count=74\n14:40:26.301027 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_vio.c:    9   6:| | | | | | <mysqlnd_vio::network_read (total=11 own=11 in_calls=0)\n14:40:26.301030 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_wireprotocol.c:   31   5:| | | | | <mysqlnd_read_packet_header_and_body (total=737 own=22 in_calls=715)\n14:40:26.301033 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_wireprotocol.c:   40   5:| | | | | info : 4.1 server_caps=65535\n\n14:40:26.301035 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_wireprotocol.c:   43   5:| | | | | info : additional 5.5+ caps=57343\n\n14:40:26.301038 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_wireprotocol.c:   45   5:| | | | | info : proto=10 server=8.0.36 thread_id=8\n14:40:26.301040 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_wireprotocol.c:   45   5:| | | | | info : server_capabilities=3758096383 charset_no=255 server_status=2 auth_protocol=caching_sha2_password scramble_length=21\n14:40:26.301044 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_wireprotocol.c:   46   4:| | | | <php_mysqlnd_greet_read (total=778 own=41 in_calls=737)\n$<\/code><\/pre>\n<p>--character-set-server=ujis \u306e\u3068\u304d\u306f charset_no=12 \u304c\u8fd4\u3063\u3066\u304d\u3066\u3044\u307e\u3059\u3002<\/p>\n<pre><code>$ sed -n '\/>.*greet_read.*\/,\/<.*greet_read.*\/p' \/tmp\/client.trace\n14:41:36.132148 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_wireprotocol.c:   33   4:| | | | >php_mysqlnd_greet_read\n14:41:36.132151 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_wireprotocol.c:   29   5:| | | | | >mysqlnd_read_packet_header_and_body\n14:41:36.132153 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_wireprotocol.c:   29   6:| | | | | | info : buf=0x7fff644e9a00 size=2048\n14:41:36.132155 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_wireprotocol.c:   24   6:| | | | | | >mysqlnd_read_header\n14:41:36.132157 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_wireprotocol.c:   24   7:| | | | | | | info : compressed=0\n14:41:36.132159 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_protocol_frame_codec.c:   32   7:| | | | | | | >mysqlnd_pfc::receive\n14:41:36.132162 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_protocol_frame_codec.c:   37   7:| | | | | | | <mysqlnd_pfc::receive (total=1 own=1 in_calls=0)\n14:41:36.132164 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_vio.c:   71   7:| | | | | | | >mysqlnd_vio::get_stream\n14:41:36.132166 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_vio.c:   71   8:| | | | | | | | info : 0x7f8dd3683600\n14:41:36.132168 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_vio.c:   71   7:| | | | | | | <mysqlnd_vio::get_stream (total=2 own=2 in_calls=0)\n14:41:36.132171 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_vio.c:    8   7:| | | | | | | >mysqlnd_vio::network_read\n14:41:36.132173 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_vio.c:    8   8:| | | | | | | | info : count=4\n14:41:36.133045 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_vio.c:    9   7:| | | | | | | <mysqlnd_vio::network_read (total=872 own=872 in_calls=0)\n14:41:36.133072 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_wireprotocol.c:   25   7:| | | | | | | info : HEADER: prot_packet_no=0 size= 74\n14:41:36.133076 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_wireprotocol.c:   26   6:| | | | | | <mysqlnd_read_header (total=919 own=44 in_calls=875)\n14:41:36.133080 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_protocol_frame_codec.c:   32   6:| | | | | | >mysqlnd_pfc::receive\n14:41:36.133082 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_protocol_frame_codec.c:   37   6:| | | | | | <mysqlnd_pfc::receive (total=0 own=0 in_calls=0)\n14:41:36.133085 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_vio.c:   71   6:| | | | | | >mysqlnd_vio::get_stream\n14:41:36.133087 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_vio.c:   71   7:| | | | | | | info : 0x7f8dd3683600\n14:41:36.133089 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_vio.c:   71   6:| | | | | | <mysqlnd_vio::get_stream (total=2 own=2 in_calls=0)\n14:41:36.133092 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_vio.c:    8   6:| | | | | | >mysqlnd_vio::network_read\n14:41:36.133094 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_vio.c:    8   7:| | | | | | | info : count=74\n14:41:36.133100 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_vio.c:    9   6:| | | | | | <mysqlnd_vio::network_read (total=6 own=6 in_calls=0)\n14:41:36.133103 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_wireprotocol.c:   31   5:| | | | | <mysqlnd_read_packet_header_and_body (total=951 own=24 in_calls=927)\n14:41:36.133106 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_wireprotocol.c:   40   5:| | | | | info : 4.1 server_caps=65535\n\n14:41:36.133108 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_wireprotocol.c:   43   5:| | | | | info : additional 5.5+ caps=57343\n\n14:41:36.133111 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_wireprotocol.c:   45   5:| | | | | info : proto=10 server=8.0.36 thread_id=8\n14:41:36.133114 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_wireprotocol.c:   45   5:| | | | | info : server_capabilities=3758096383 charset_no=12 server_status=2 auth_protocol=caching_sha2_password scramble_length=21\n14:41:36.133117 \/home\/sejima\/src\/php\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_wireprotocol.c:   46   4:| | | | <php_mysqlnd_greet_read (total=967 own=16 in_calls=951)\n$<\/code><\/pre>\n<p>\u3053\u3053\u3067MySQL\u306e\u8a8d\u8a3c\u5468\u308a\u3001handshake\u306b\u3064\u3044\u3066\u632f\u308a\u8fd4\u3063\u3066\u307f\u307e\u3059\u3002<\/p>\n<p><a href=\"https:\/\/dev.mysql.com\/doc\/dev\/mysql-server\/latest\/page_protocol_connection_phase.html#sect_protocol_connection_phase_initial_handshake\">https:\/\/dev.mysql.com\/doc\/dev\/mysql-server\/latest\/page_protocol_connection_phase.html#sect_protocol_connection_phase_initial_handshake<\/a><\/p>\n<blockquote><p>\n  The initial handshake starts with the server sending the Protocol::Handshake packet.\n<\/p><\/blockquote>\n<p>\u3068\u3042\u308a\u307e\u3059\u306d\u3002Handshake Packet\u3067\u3059\u304c<\/p>\n<p><a href=\"https:\/\/dev.mysql.com\/doc\/dev\/mysql-server\/latest\/page_protocol_connection_phase_packets_protocol_handshake_v10.html\">https:\/\/dev.mysql.com\/doc\/dev\/mysql-server\/latest\/page_protocol_connection_phase_packets_protocol_handshake_v10.html<\/a><\/p>\n<p>\u3092\u898b\u308b\u3068\u3001<\/p>\n<blockquote><p>\n  int<1>    character_set   default server a_protocol_character_set, only the lower 8-bits\n<\/p><\/blockquote>\n<p>\u3068\u3044\u3063\u305f\u30d5\u30a3\u30fc\u30eb\u30c9\u304c\u3042\u308a\u307e\u3059\u3002character set \u306eID\u306f\u3001<a href=\"https:\/\/dev.mysql.com\/doc\/dev\/mysql-server\/latest\/page_protocol_basic_character_set.html#a_protocol_character_set\">Source Code Documentation<br \/>\n<\/a> \u3092\u53c2\u7167\u3059\u308b\u3068\u3001information_schema.collations\u304b\u3089 collation_name \u3092\u53d6\u5f97\u3067\u304d\u308b\u3068\u308f\u304b\u308a\u307e\u3059\u3002<\/p>\n<p>\u5b9f\u969b\u306b information_schema.collations \u306bSELECT\u3057\u3066\u307f\u308b\u3068<\/p>\n<pre class=\"lang:sql\"><code>mysql> SELECT id, collation_name FROM information_schema.collations WHERE id IN (12,255);\n+-----+--------------------+\n| id  | collation_name     |\n+-----+--------------------+\n|  12 | ujis_japanese_ci   |\n| 255 | utf8mb4_0900_ai_ci |\n+-----+--------------------+\n2 rows in set (0.01 sec)\n\nmysql><\/code><\/pre>\n<p>charset_no=255\u306futf8mb4_0900_ai_ci\u3001charset_no=12\u306fujis_japanese_ci\u3068\u308f\u304b\u308a\u307e\u3059\u3002<\/p>\n<p>mysqlnd_command::handshake() \u304b\u3089\u30bd\u30fc\u30b9\u30b3\u30fc\u30c9\u3092\u8aad\u307f\u9032\u3081\u3066\u3044\u304f\u3068\u3001 greet_packet.charset_no \u3092\u95a2\u6570\u306e\u5f15\u6570\u3067\u6e21\u3057\u3066\u3044\u3063\u3066<\/p>\n<p><a href=\"https:\/\/github.com\/php\/php-src\/blob\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_commands.c#L603-L658\">https:\/\/github.com\/php\/php-src\/blob\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_commands.c#L603-L658<\/a><br \/>\n<a href=\"https:\/\/github.com\/php\/php-src\/blob\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_auth.c#L202-L224\">https:\/\/github.com\/php\/php-src\/blob\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_auth.c#L202-L224<\/a><br \/>\n<a href=\"https:\/\/github.com\/php\/php-src\/blob\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_auth.c#L36-L122\">https:\/\/github.com\/php\/php-src\/blob\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_auth.c#L36-L122<\/a><br \/>\n<a href=\"https:\/\/github.com\/php\/php-src\/blob\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_auth.c#L233-L289\">https:\/\/github.com\/php\/php-src\/blob\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_auth.c#L233-L289<\/a><\/p>\n<p>session_options->charset_name \u304c\u8a2d\u5b9a\u3055\u308c\u3066\u3044\u306a\u3051\u308c\u3070\u3001\u95a2\u6570\u306e\u5f15\u6570\u3067\u6e21\u3055\u308c\u3066\u304d\u305fserver_charset_no\u3092auth_packet.charset_no\u306b\u8a2d\u5b9a\u3057\u3066\u3044\u307e\u3059\u3002<\/p>\n<pre class=\"start-line:285 lang:c++\"><code>     if (session_options->charset_name && (charset = mysqlnd_find_charset_name(session_options->charset_name))) {\n            auth_packet.charset_no  = charset->nr;\n        } else {\n            auth_packet.charset_no  = server_charset_no;\n        }<\/code><\/pre>\n<p>auth_packet.charset_no \u3068\u5b9f\u969b\u306e character set \u3084 collation \u306e\u30de\u30c3\u30d4\u30f3\u30b0\u306f<\/p>\n<p><a href=\"https:\/\/github.com\/php\/php-src\/blob\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_auth.c#L310\">https:\/\/github.com\/php\/php-src\/blob\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_auth.c#L310<\/a><br \/>\n<a href=\"https:\/\/github.com\/php\/php-src\/blob\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_charset.c#L741-L752\">https:\/\/github.com\/php\/php-src\/blob\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_charset.c#L741-L752<\/a><br \/>\n<a href=\"https:\/\/github.com\/php\/php-src\/blob\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_charset.c#L496\">https:\/\/github.com\/php\/php-src\/blob\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_charset.c#L496<\/a><\/p>\n<pre class=\"start-line:496 lang:c++\"><code>    {  12, \"ujis\", \"ujis_japanese_ci\", 1, 3, \"\", mysqlnd_mbcharlen_ujis, check_mb_ujis},<\/code><\/pre>\n<p><a href=\"https:\/\/github.com\/php\/php-src\/blob\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_charset.c#L688\">https:\/\/github.com\/php\/php-src\/blob\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_charset.c#L688<\/a><\/p>\n<pre class=\"start-line:688 lang:c++\"><code>    { 255, UTF8_MB4, UTF8_MB4\"_0900_ai_ci\", 1, 4, \"\", mysqlnd_mbcharlen_utf8, check_mb_utf8_valid},<\/code><\/pre>\n<p>\u3068\u3044\u3046\u3088\u3046\u306b\u3001mysqlnd\u5185\u90e8\u3067\u5b9a\u7fa9\u3055\u308c\u3066\u3044\u308b\u308f\u3051\u3067\u3059\u306d\u3002<\/p>\n<h2>mysqlnd \u306b\u304a\u3051\u308b SET NAMES \u306e\u6ce8\u610f\u4e8b\u9805<\/h2>\n<p><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/ja\/set-names.html\">SET NAMES \u30b9\u30c6\u30fc\u30c8\u30e1\u30f3\u30c8<\/a>\u3092\u4f7f\u3046\u3053\u3068\u3067character_set_client\u3001character_set_connection \u304a\u3088\u3073 character_set_results\u3092\u5909\u66f4\u3059\u308b\u3053\u3068\u304c\u53ef\u80fd\u3067\u306f\u3042\u308b\u3093\u3067\u3059\u304c\u3001\u30af\u30e9\u30a4\u30a2\u30f3\u30c8\u30e9\u30a4\u30d6\u30e9\u30ea\u306b\u3088\u3063\u3066\u306f\u554f\u984c\u304c\u767a\u751f\u3059\u308b\u3053\u3068\u3082\u3042\u308a\u307e\u3059\u3002\u4f8b\u3048\u3070\u3001PHP\u306e<a href=\"https:\/\/www.php.net\/manual\/ja\/mysqli.real-escape-string.php\">mysqli_real_escape_string<\/a>\u306e\u30c9\u30ad\u30e5\u30e1\u30f3\u30c8\u306b\u306f<\/p>\n<blockquote><p>\n  \u30b5\u30fc\u30d0\u30fc\u30ec\u30d9\u30eb\u3067\u8a2d\u5b9a\u3059\u308b\u306a\u308a API \u95a2\u6570 <a href=\"https:\/\/www.php.net\/manual\/ja\/mysqli.set-charset.php\">mysqli_set_charset()<\/a> \u3092\u4f7f\u3046\u306a\u308a\u3057\u3066\u3001 \u6587\u5b57\u30bb\u30c3\u30c8\u3092\u660e\u793a\u3057\u3066\u304a\u304f\u5fc5\u8981\u304c\u3042\u308a\u307e\u3059\u3002\u3053\u306e\u6587\u5b57\u30bb\u30c3\u30c8\u304c mysqli_real_escape_string() \u306b\u5f71\u97ff\u3092\u53ca\u307c\u3057\u307e\u3059\u3002\u8a73\u7d30\u306f <a href=\"https:\/\/www.php.net\/manual\/ja\/mysqlinfo.concepts.charset.php\">\u6587\u5b57\u30bb\u30c3\u30c8\u306e\u6982\u5ff5<\/a> \u3092\u53c2\u7167\u304f\u3060\u3055\u3044\u3002\n<\/p><\/blockquote>\n<p>\u3068\u3042\u308a\u307e\u3059\u3002<\/p>\n<p>\u5177\u4f53\u7684\u306b\u306f\u3001<a href=\"https:\/\/github.com\/php\/php-src\/blob\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_connection.c#L924-L936\">mysqlnd_conn_data::escape_string()<\/a>\u3067 <a href=\"https:\/\/github.com\/php\/php-src\/blob\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_charset.c#L827-L857\">mysqlnd_cset_escape_slashes()<\/a> \u3092\u547c\u3076\u3068<\/p>\n<pre class=\"start-line:843 lang:c++\"><code>        \/* check unicode characters *\/\n        if (cset->char_maxlen > 1 && (len = cset->mb_valid(escapestr, end))) {<\/code><\/pre>\n<p>\u3084<\/p>\n<pre class=\"start-line:857 lang:c++\"><code>        if (cset->char_maxlen > 1 && cset->mb_charlen(*escapestr) > 1) {<\/code><\/pre>\n<p>\u3068\u3044\u3063\u305f\u7b87\u6240\u304c\u3042\u308a\u307e\u3059\u304c\u3001\u3053\u306e <a href=\"https:\/\/github.com\/php\/php-src\/blob\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_structs.h#L186-L187\">cset->mb_valid() \u3084 cset->mb_charlen()<\/a> \u304c\u3001\u3055\u304d\u307b\u3069\u898b\u305f<\/p>\n<pre class=\"start-line:496 lang:c++\"><code>    {  12, \"ujis\", \"ujis_japanese_ci\", 1, 3, \"\", mysqlnd_mbcharlen_ujis, check_mb_ujis},<\/code><\/pre>\n<p>\u3084<\/p>\n<pre class=\"start-line:688 lang:c++\"><code>    { 255, UTF8_MB4, UTF8_MB4\"_0900_ai_ci\", 1, 4, \"\", mysqlnd_mbcharlen_utf8, check_mb_utf8_valid},<\/code><\/pre>\n<p>\u306e mysqlnd_mbcharlen_ujis \u3084 check_mb_ujis \u3001mysqlnd_mbcharlen_utf8 \u3084 check_mb_utf8_valid \u306a\u308f\u3051\u3067\u3059\u306d\u3002<br \/>\n\u4f8b\u3048\u3070\u3001 utf8mb4_general_ci \u306f<\/p>\n<p><a href=\"https:\/\/github.com\/php\/php-src\/blob\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_charset.c#L532\">https:\/\/github.com\/php\/php-src\/blob\/php-8.1.27\/ext\/mysqlnd\/mysqlnd_charset.c#L532<\/a><\/p>\n<pre class=\"start-line:532 lang:c++\"><code>    {  45, UTF8_MB4, UTF8_MB4\"_general_ci\", 1, 4, \"UTF-8 Unicode\", mysqlnd_mbcharlen_utf8,  check_mb_utf8_valid},<\/code><\/pre>\n<p>mysqlnd_mbcharlen_utf8\u3068check_mb_utf8_valid\u306a\u306e\u3067\u3001utf8mb4_0900_ai_ci\u3068\u4e92\u63db\u6027\u304c\u3042\u308a\u305d\u3046\u3067\u3059\u3051\u3069\u3001mysqlnd_mbcharlen_ujis\u3084check_mb_ujis\u306eujis\u3067\u306f\u6587\u5b57\u5217\u306e\u30a8\u30b9\u30b1\u30fc\u30d7\u304c\u3046\u307e\u304f\u52d5\u304b\u306a\u305d\u3046\u3060\u3001\u3068\u3044\u3046\u3068\u3053\u308d\u3067\u3059\u3002<\/p>\n<p>mb_charlen\u3084mb_valid\u306a\u3069\u3082\u5207\u308a\u66ff\u3048\u308b\u305f\u3081\u306b\u3001PHP\u306emysqli_real_escape_string\u306e\u30c9\u30ad\u30e5\u30e1\u30f3\u30c8\u306b\u306f\u3001mysqli_set_charset()\u3092\u4f7f\u3046\u3088\u3046\u66f8\u3044\u3066\u3042\u308b\u308f\u3051\u3067\u3059\u306d\u3002<\/p>\n<p>\u3053\u3046\u3044\u3063\u305f\u30a8\u30b9\u30b1\u30fc\u30d7\u306b\u95a2\u3059\u308b\u632f\u308b\u821e\u3044\u306f\u3001\u5404\u30d7\u30ed\u30b0\u30e9\u30df\u30f3\u30b0\u8a00\u8a9e\u306e\u30e9\u30a4\u30d6\u30e9\u30ea\u9593\u3067\u6319\u52d5\u304c\u7570\u306a\u308b\u53ef\u80fd\u6027\u3082\u5426\u3081\u307e\u305b\u3093\u3002\u30e9\u30a4\u30d6\u30e9\u30ea\u3067character set\u3092\u3069\u306e\u3088\u3046\u306b\u8a2d\u5b9a\u3059\u3079\u304d\u306a\u306e\u304b\u306f\u3001\u30e9\u30a4\u30d6\u30e9\u30ea\u306e\u30c9\u30ad\u30e5\u30e1\u30f3\u30c8\u7b49\u78ba\u8a8d\u3055\u308c\u308b\u306e\u304c\u3088\u308d\u3057\u3044\u3067\u3057\u3087\u3046\u3002<\/p>\n<h2>\u304a\u307e\u3051\uff1a init_connet<\/h2>\n<p>\u300cujis\u3068utf8mb4\u304f\u3089\u3044\u9055\u3046\u306a\u3089\u3057\u3087\u3046\u304c\u306a\u3044\u3051\u3069\u3001collation\u304f\u3089\u3044\u306a\u3089\u306a\u3093\u3068\u304b\u30a4\u30f3\u30c1\u30ad\u3067\u304d\u3093\u306e\u304b\uff1f\u300d<\/p>\n<p>\u305d\u3046\u304a\u8003\u3048\u306e\u65b9\u3082\u3044\u3089\u3063\u3057\u3083\u308b\u3067\u3057\u3087\u3046\u3002\u79c1\u306f\u8003\u3048\u307e\u3059\u3002<\/p>\n<p>\u3068\u3044\u3046\u308f\u3051\u3067\u3001 --skip-character-set-client-handshake \u3092\u4f7f\u308f\u305a\u306b <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/ja\/server-system-variables.html#sysvar_init_connect\">--init-connect<\/a> \u3067 collation \u3092\u5f37\u5236\u7684\u306b\u4e0a\u66f8\u304d\u3057\u3066\u307f\u3088\u3046\u3068\u601d\u3044\u307e\u3059\u3002<\/p>\n<p>\u6b21\u306e\u3088\u3046\u306b\u6307\u5b9a\u3057\u3064\u3064mysqld\u8d77\u52d5\u3057\u3066<\/p>\n<pre><code>$ docker run -p 127.0.0.1:13306:3306 -e MYSQL_ROOT_HOST=172.17.0.1 -e MYSQL_ROOT_PASSWORD=sejima -e MYSQL_USER=bang -e MYSQL_PASSWORD=brave --name mysql8036 -d container-registry.oracle.com\/mysql\/community-server:8.0.36 --init-connect=\"SET NAMES utf8mb4 COLLATE utf8mb4_general_ci; SET SESSION default_collation_for_utf8mb4=utf8mb4_general_ci; SET SESSION collation_database=utf8mb4_general_ci; SET SESSION collation_server=utf8mb4_general_ci;\"\n<\/code><\/pre>\n<p>\u307e\u305a\u306fmysql\u30af\u30e9\u30a4\u30a2\u30f3\u30c8\u3067\u8a66\u3057\u3066\u307f\u307e\u3057\u3087\u3046\u3002root\u306fSUPER\u6a29\u9650\u304c\u3042\u308b\u306e\u3067\u3001init_connect \u306e\u5f71\u97ff\u3092\u53d7\u3051\u307e\u305b\u3093\u3002LANG=ja_JP.eucjp\u3067\u306fcharacter_set_client=ujis\u306b\u306a\u308a\u307e\u3059\u3002\u307e\u305f\u3001collation_server\u306a\u3069\u306fMySQL8.0\u306e\u30c7\u30d5\u30a9\u30eb\u30c8\u3067\u3042\u308butf8mb4_0900_ai_ci\u306b\u306a\u308a\u307e\u3059\u3002<\/p>\n<pre><code>$ LANG=ja_JP.eucjp MYSQL_PWD=sejima mysql -u root -e \"show variables like '%character%'\" -h 127.0.0.1 -P 13306\n+--------------------------+--------------------------------+\n| Variable_name            | Value                          |\n+--------------------------+--------------------------------+\n| character_set_client     | ujis                           |\n| character_set_connection | ujis                           |\n| character_set_database   | utf8mb4                        |\n| character_set_filesystem | binary                         |\n| character_set_results    | ujis                           |\n| character_set_server     | utf8mb4                        |\n| character_set_system     | utf8mb3                        |\n| character_sets_dir       | \/usr\/share\/mysql-8.0\/charsets\/ |\n+--------------------------+--------------------------------+\n$ LANG=ja_JP.eucjp MYSQL_PWD=sejima mysql -u root -e \"show variables like '%collation%'\" -h 127.0.0.1 -P 13306\n+-------------------------------+--------------------+\n| Variable_name                 | Value              |\n+-------------------------------+--------------------+\n| collation_connection          | ujis_japanese_ci   |\n| collation_database            | utf8mb4_0900_ai_ci |\n| collation_server              | utf8mb4_0900_ai_ci |\n| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |\n+-------------------------------+--------------------+\n$\n<\/code><\/pre>\n<p>SUPER\u6a29\u9650\u3082CONNECTION_ADMIN\u6a29\u9650\u3082\u306a\u3044\u30e6\u30fc\u30b6\u3067\u30a2\u30af\u30bb\u30b9\u3059\u308b\u3068\u3001\u6b21\u306e\u3088\u3046\u306b\u66f8\u304d\u63db\u308f\u308a\u307e\u3059\u3002<\/p>\n<pre><code>$ LANG=ja_JP.eucjp MYSQL_PWD=brave mysql -u bang -e \"show variables like '%character%'\" -h 127.0.0.1 -P 13306\n+--------------------------+--------------------------------+\n| Variable_name            | Value                          |\n+--------------------------+--------------------------------+\n| character_set_client     | utf8mb4                        |\n| character_set_connection | utf8mb4                        |\n| character_set_database   | utf8mb4                        |\n| character_set_filesystem | binary                         |\n| character_set_results    | utf8mb4                        |\n| character_set_server     | utf8mb4                        |\n| character_set_system     | utf8mb3                        |\n| character_sets_dir       | \/usr\/share\/mysql-8.0\/charsets\/ |\n+--------------------------+--------------------------------+\n$ LANG=ja_JP.eucjp MYSQL_PWD=brave mysql -u bang -e \"show variables like '%collation%'\" -h 127.0.0.1 -P 13306\n+-------------------------------+--------------------+\n| Variable_name                 | Value              |\n+-------------------------------+--------------------+\n| collation_connection          | utf8mb4_general_ci |\n| collation_database            | utf8mb4_general_ci |\n| collation_server              | utf8mb4_general_ci |\n| default_collation_for_utf8mb4 | utf8mb4_general_ci |\n+-------------------------------+--------------------+\n$\n<\/code><\/pre>\n<p>\u6b21\u306f\u3001go-sql-driver\/mysql\u3067\u78ba\u8a8d\u3057\u3066\u307f\u307e\u3057\u3087\u3046\u3002sed \u3092\u4f7f\u3063\u3066 test.go \u306e\u30a2\u30ab\u30a6\u30f3\u30c8\u3068\u30d1\u30b9\u30ef\u30fc\u30c9\u3092\u66f8\u304d\u63db\u3048\u307e\u3059\u3002<\/p>\n<pre><code>$ sed -i.orig -e 's\/root\/bang\/' -e 's\/sejima\/brave\/' test.go\n<\/code><\/pre>\n<p>\u6b21\u306e\u3088\u3046\u306b\u3001 --character-set-client-handshake=off \u3092\u4f7f\u308f\u306a\u304f\u3066\u3082\u3001collation_server\u3084collation_database\u3092utf8mb4_general_ci\u3067\u7f6e\u304d\u63db\u3048\u308b\u3053\u3068\u304c\u53ef\u80fd\u3067\u3059\u3002<\/p>\n<pre><code>$ go run test.go\ncharacter_set_client (utf8mb4)\ncharacter_set_connection (utf8mb4)\ncharacter_set_database (utf8mb4)\ncharacter_set_filesystem (binary)\ncharacter_set_results (utf8mb4)\ncharacter_set_server (utf8mb4)\ncharacter_set_system (utf8mb3)\ncharacter_sets_dir (\/usr\/share\/mysql-8.0\/charsets\/)\ncollation_connection (utf8mb4_general_ci)\ncollation_database (utf8mb4_general_ci)\ncollation_server (utf8mb4_general_ci)\n$\n<\/code><\/pre>\n<p>\u305f\u3060\u3057\u3001\u3053\u308c\u306fcollation_connection\u3084collation_server\u306a\u3069\u66f8\u304d\u63db\u3048\u3089\u308c\u305f\u3060\u3051\u306a\u306e\u3067\u3001\u3053\u308c\u3067\u30af\u30e9\u30a4\u30a2\u30f3\u30c8\u30e9\u30a4\u30d6\u30e9\u30ea\u304c\u6587\u5b57\u5217\u306e\u30a8\u30b9\u30b1\u30fc\u30d7\u306a\u3069\u6b63\u3057\u304f\u5b9f\u884c\u3067\u304d\u308b\u304b\u3069\u3046\u304b\u306f\u3001\u30e9\u30a4\u30d6\u30e9\u30ea\u306e\u30c9\u30ad\u30e5\u30e1\u30f3\u30c8\u306a\u3069\u8981\u78ba\u8a8d\u3067\u3059\u306d\u3002<\/p>\n<p>\u203b\u4eca\u56de\u3001 --init-connect \u3067 <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/ja\/server-system-variables.html#sysvar_default_collation_for_utf8mb4\">default_collation_for_utf8mb4<\/a> \u3068\u3044\u3046\u30b7\u30b9\u30c6\u30e0\u5909\u6570\u3092\u6307\u5b9a\u3057\u307e\u3057\u305f\u3002\u30c7\u30d5\u30a9\u30eb\u30c8\u306ecollation\u3092utf8mb4_0900_ai_ci\u304b\u3089\u5909\u66f4\u3059\u308b\u5834\u5408\u3001default_collation_for_utf8mb4\u306b\u3064\u3044\u3066\u3082\u8003\u616e\u3057\u305f\u65b9\u304c\u826f\u3044\u306e\u3067\u6307\u5b9a\u3057\u307e\u3057\u305f\u304c\u3001\u3053\u306e\u30b7\u30b9\u30c6\u30e0\u5909\u6570\u306b\u3064\u3044\u3066\u3082\u89e3\u8aac\u3057\u3088\u3046\u3068\u3059\u308b\u3068\u3001\u305d\u308c\u3060\u3051\u3067\u8a18\u4e8b\u304c\u4e00\u672c\u66f8\u3051\u3066\u3057\u307e\u3046\u30b7\u30ed\u30e2\u30ce\u306b\u306a\u308a\u307e\u3059\u3002\u3086\u3048\u306b\u3001\u4eca\u56de\u306e\u8a18\u4e8b\u3067\u306fdefault_collation_for_utf8mb4\u306e\u8a73\u7d30\u306b\u3064\u3044\u3066\u306f\u89e6\u308c\u307e\u305b\u3093\u3002<br \/>\n\u5b9f\u969b\u3001<a href=\"https:\/\/gihyo.jp\/dev\/serial\/01\/mysql-road-construction-news\/0157\">\u5317\u5ddd\u3055\u3093\u304cdefault_collation_for_utf8mb4\u306a\u3069\u3067\u89e3\u8aac\u8a18\u4e8b\u3092\u4e00\u672c\u57f7\u7b46\u3055\u308c\u3066\u3044\u307e\u3059<\/a>\u306e\u3067\u3001\u6c17\u306b\u306a\u308b\u65b9\u306f\u3053\u3061\u3089\u306e\u8a18\u4e8b\u3092\u53c2\u7167\u3057\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\n<h2>\u304a\u308f\u308a\u306b<\/h2>\n<p>\u632f\u308a\u8fd4\u3063\u3066\u307f\u308b\u3068\u3001\u500b\u4eba\u7684\u306b\u306f<\/p>\n<ul>\n<li>character set\u3084collation\u306e\u30c7\u30d5\u30a9\u30eb\u30c8\u306e\u632f\u308b\u821e\u3044\u304c\u30af\u30e9\u30a4\u30a2\u30f3\u30c8\u30e9\u30a4\u30d6\u30e9\u30ea\u5074\u3067\u7570\u306a\u3063\u3066\u3044\u308b\u306e\u3067\u3001 --skip-character-set-client-handshake \u3067\u30b5\u30fc\u30d0\u5074\u306e\u8a2d\u5b9a\u3092\u5f37\u5236\u3055\u305b\u308b\u3053\u3068\u306b\u3088\u308a\u3001\u5404\u30d7\u30ed\u30b0\u30e9\u30df\u30f3\u30b0\u8a00\u8a9e\u3054\u3068\u306e\u632f\u308b\u821e\u3044\u306e\u9055\u3044\u3092\u5438\u53ce\u3057\u3084\u3059\u304b\u3063\u305f\u3002<\/li>\n<\/ul>\n<p>\u3068\u3044\u3046\u4e00\u9762\u3082\u3042\u3063\u305f\u3093\u3058\u3083\u306a\u3044\u304b\u3068\u601d\u3044\u307e\u3057\u305f\u3002\u305f\u3060\u3001\u53e4\u3044\u30bd\u30fc\u30b9\u30b3\u30fc\u30c9\u3084\u6a5f\u80fd\u3092\u6574\u7406\u3057\u3066\u3044\u304f\u3053\u3068\u306f\u3001\u30bd\u30d5\u30c8\u30a6\u30a7\u30a2\u3092\u9577\u671f\u7684\u306b\u30e1\u30f3\u30c6\u30ca\u30f3\u30b9\u3057\u3066\u3044\u304f\u4e0a\u3067\u5927\u5207\u306a\u3053\u3068\u3060\u3068\u601d\u3044\u307e\u3059\u304b\u3089\u3001 --skip-character-set-client-handshake \u3092\u5ec3\u6b62\u3059\u308b\u30e1\u30ea\u30c3\u30c8\u3082\u7406\u89e3\u3067\u304d\u308b\u3068\u3053\u308d\u3067\u3059\u3002\u5c11\u306a\u304f\u3068\u3082\u3001\u30d7\u30ed\u30b0\u30e9\u30df\u30f3\u30b0\u8a00\u8a9e\u306e\u30e9\u30a4\u30d6\u30e9\u30ea\u3054\u3068\u306b\u3053\u308c\u3060\u3051\u632f\u308b\u821e\u3044\u304c\u7570\u306a\u3063\u3066\u3001\u3055\u3089\u306b\u3001 --character-set-client-handshake \u306e on\/off \u3067\u307e\u305f\u632f\u308b\u821e\u3044\u304c\u5909\u308f\u308b\u306e\u3067\u3042\u308c\u3070\u3001\u8907\u96d1\u602a\u5947\u3068\u3044\u3046\u4ed6\u3042\u308a\u307e\u305b\u3093\u3002\u5206\u5c90\u3059\u308b\u6761\u4ef6\u306f\u6e1b\u3089\u305b\u308b\u306a\u3089\u6e1b\u3089\u3057\u305f\u3044\u3068\u601d\u3046\u306e\u306f\u3001\u81ea\u7136\u306a\u3053\u3068\u3060\u308d\u3046\u3068\u601d\u3044\u307e\u3059\u3002<\/p>\n<p>\u3068\u308a\u3042\u3048\u305a\u3001PHP\u3067 --skip-character-set-client-handshake \u306e\u8a2d\u5b9a\u3055\u308c\u305f mysqld \u4f7f\u3063\u3066\u305f\u65b9\u3005\u306f\u3001\u305d\u3093\u306a\u306b\u8003\u3048\u308b\u3053\u3068\u306f\u306a\u3044\u304b\u3082\u3057\u308c\u307e\u305b\u3093\u3002\u305f\u3060\u3001PHP\u4ee5\u5916\u306e\u30d7\u30ed\u30b0\u30e9\u30df\u30f3\u30b0\u8a00\u8a9e\u3092\u4f7f\u308f\u308c\u3066\u3044\u3066\u4eca\u5f8cMySQL\u306e\u30d0\u30fc\u30b8\u30e7\u30f3\u30a2\u30c3\u30d7\u3092\u4e88\u5b9a\u3055\u308c\u3066\u3044\u308b\u65b9\u3005\u306f\u3001character set \u3084 collation \u306e\u8a2d\u5b9a\u3092\u3001\u4e00\u5ea6\u898b\u76f4\u3057\u3066\u307f\u308b\u3068\u3088\u308d\u3057\u3044\u306e\u304b\u3082\u3057\u308c\u307e\u305b\u3093\u3002<\/p>\n<h2>References<\/h2>\n<ul>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/ja\/charset-connection.html\">10.4 \u63a5\u7d9a\u6587\u5b57\u30bb\u30c3\u30c8\u304a\u3088\u3073\u7167\u5408\u9806\u5e8f<\/a><\/li>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/c-api\/8.0\/en\/mysql-options.html\">5.4.54 mysql_options()<\/a><\/li>\n<li><a href=\"https:\/\/gihyo.jp\/dev\/serial\/01\/mysql-road-construction-news\/0065\">\u7b2c65\u56de MySQL\u3068\u6587\u5b57\u30b3\u30fc\u30c9 | gihyo.jp<\/a><\/li>\n<li><a href=\"https:\/\/gihyo.jp\/dev\/serial\/01\/mysql-road-construction-news\/0157\">\u7b2c157\u56de MySQL\u306e\u30c7\u30d5\u30a9\u30eb\u30c8collation\u306e\u6ce8\u610f\u70b9 | gihyo.jp<\/a><\/li>\n<li><a href=\"https:\/\/mita2db.hateblo.jp\/entry\/2020\/12\/07\/000000\">MySQL 8.0 \u3067\u3082 utf8mb4_general_ci \u3092\u4f7f\u3044\u7d9a\u3051\u305f\u3044\u50d5\u3089\u306f | mita2 database life<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>\u3053\u3093\u306b\u3061\u308f\u3002\u305b\u3058\u307e\u3067\u3059\u3002 \u76f8\u5909\u308f\u3089\u305aPHP\u3088\u304f\u308f\u304b\u3093\u306a\u3044\u3093\u3067\u3059\u304c\u3001\u4eca\u65e5\u3082PHP\u306e\u8a71\u3092\u3057\u307e\u3059\u3002\u3042\u3068\u3001go\u306e\u8a71\u3082\u3061\u3087\u3063\u3068\u3060\u3051\u3057\u307e\u3059\u3002\u66f8\u3044\u3066\u308b\u3046\u3061\u306b\u5c11\u3057\u9577\u6587\u306b\u306a\u3063\u3066\u3057\u307e\u3044\u307e\u3057\u305f\u304c\u3001\u5185\u5bb9\u3068\u3057\u3066\u306f\u3086\u308b\u3075\u308f\u3068\u8a00\u3048\u308b\u3093\u3058\u3083\u306a\u3044\u3067\u3057\u3087\u3046 [&hellip;]<\/p>\n","protected":false},"author":137,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[9],"tags":[17,8],"class_list":["post-23110","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-info","tag-mysql","tag-php"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/posts\/23110","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/users\/137"}],"replies":[{"embeddable":true,"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/comments?post=23110"}],"version-history":[{"count":11,"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/posts\/23110\/revisions"}],"predecessor-version":[{"id":23123,"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/posts\/23110\/revisions\/23123"}],"wp:attachment":[{"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/media?parent=23110"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/categories?post=23110"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/tags?post=23110"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}