MySQLのひらがなカタカナ、英数字の全角半角を区別する。COLLATEE(照合順序)設定

※ 本ページはプロモーションが含まれています。

このページの目的と動作環境
MySQLのDBでひらがなカタカナやカタカナ英数字の全角半角が区別しない問題を調査。原因はCOLLATION（照合順序）の指定
MySQLのDB作成時にCOLLATIONを指定して、ひらがなカタカナや英数字の全角半角が区別する
MySQLでひらがなカタカナや英数字の全角半角の区別されないDBのCOLLATIONの設定を変更して区別する
MySQLのSQLの条件式でCOLLATEを指定する事で、ひらがなカタカナ、英数字の全角半角を区別する

このページの目的と動作環境

最終更新日：2023/9/29

MySQLをインストールして使い始めてある程度経ってから気づいた事なんですが、SQLの条件式(where句以降)で、テーブルのchar型・varchar型のカラム（項目）の平仮名とカタカナが区別しない事に気づきました。他にも、カタカナ・英字・数字の半角全角が区別しない問題もわかりました。

例えば、「select * from hoge_data where content = 'コンテンツ';」というSQLを実行したら、hoge_dataテーブルのcontentカラムが、'コンテンツ'だけでなく、'こんてんつ'、'ｺﾝﾃﾝﾂ'、'こんテンﾂ'等のレコードまで検索結果に出てくる感じです（平仮名カタカナが区別されない）。
また、「SELECT * FROM hoge_data where content = 'hoge';」を実行したら、hoge_dataテーブルのcontentカラムが、'hoge'だけでなく、'HOGE'、'HOge'等のレコードも検索結果に出る（英字の全角半角が区別されない）。

という事で本記事では、MySQLのカラムのひらがなカタカナや英数字の半角全角が区別されない問題の原因、MySQLのCOLLATE（照合順序）とは何かや状態確認、区別しない状態から区別する状態への設定変更の方法などについて説明しています。

◾️動作環境とバージョン情報です
OS：macOS Big Sur（バージョン11.7.7）、Linux CentOS7
MySQL：MySQL Community Server 8.0.33

私が試した環境のMySQLでは、デフォルト（初期状態）で平仮名カタカナ、英数字の全角半角が区別されなかったけど、OS環境やMySQLのバージョンによってはデフォルトで区別されている事もあると思います。ちなみにMySQLではないけどCentOS7にインストールしたMariaDBはデフォルトで区別できるようになっていました。

また、MySQLやMariaDBのインストール方法については別ページで説明しているので、良ければ参考にしてください。
MySQLのインストール【macOS】
MySQL8のインストール方法(ec2,CentOS7)
MariaDB10.6(10.5)のインストール(Amazon Linux2(ec2),CentOS7)

MySQLのDBでひらがなカタカナやカタカナ英数字の全角半角が区別しない問題を調査。原因はCOLLATION（照合順序）の指定

今回の問題についてもう少し詳細に説明していきます。

MySQLサーバにrootログインしてMySQLプロンプト上で適当なDBを作ります（ここではhogedbという名前のDBを作る）。

> create database hogedb;

今作ったhogedbを選択して、適当なテーブルを作ります（ここではhoge_dataという名前のテーブルを作る）。

> use hogedb;
Database changed

> create table hoge_data (id integer auto_increment, content varchar(512), updated_at timestamp not null, primary key (id));

hoge_dataテーブルにテスト用のレコードデータを生成します。（ちょっとSQLデータの数字(12345)の全角半角の見分けが解りにくいですがコピペすれば解ると思います。）

> INSERT INTO hoge_data(content, updated_at) VALUES 
('コンテンツ', LOCALTIME()),
('こんてんつ', LOCALTIME()),
('ｺﾝﾃﾝﾂ', LOCALTIME()),
('こんテンﾂ', LOCALTIME()),
('HOGE', LOCALTIME()),
('hoge', LOCALTIME()),
('HOge', LOCALTIME()),
('12345', LOCALTIME()),
('１２３４５', LOCALTIME()),
('12３４５', LOCALTIME());

作成したhoge_dataテーブルのレコードを確認します。

> select * from hoge_data;
+----+-----------------+---------------------+
| id | content         | updated_at          |
+----+-----------------+---------------------+
|  1 | コンテンツ      | 2023-06-20 01:39:23 |
|  2 | こんてんつ      | 2023-06-20 01:39:23 |
|  3 | ｺﾝﾃﾝﾂ           | 2023-06-20 01:39:23 |
|  4 | こんテンﾂ       | 2023-06-20 01:39:23 |
|  5 | HOGE            | 2023-06-20 01:39:23 |
|  6 | hoge            | 2023-06-20 01:39:23 |
|  7 | HOge            | 2023-06-20 01:39:23 |
|  8 | 12345           | 2023-06-20 01:39:23 |
|  9 | １２３４５      | 2023-06-20 01:39:23 |
| 10 | 12３４５        | 2023-06-20 01:39:23 |
+----+-----------------+---------------------+
10 rows in set (0.00 sec)

テスト用のDB、テーブル、レコードができたので、試しに適当なselect SQLをいくつか実行します。

> select  * from hoge_data where content = 'コンテンツ';
+----+-----------------+---------------------+
| id | content         | updated_at          |
+----+-----------------+---------------------+
|  1 | コンテンツ      | 2023-06-20 02:02:19 |
|  2 | こんてんつ      | 2023-06-20 02:02:19 |
|  3 | ｺﾝﾃﾝﾂ           | 2023-06-20 02:02:19 |
|  4 | こんテンﾂ       | 2023-06-20 02:02:19 |
+----+-----------------+---------------------+
4 rows in set (0.00 sec)

> SELECT  * FROM hoge_data where content = 'こんテンﾂ';
+----+-----------------+---------------------+
| id | content         | updated_at          |
+----+-----------------+---------------------+
|  1 | コンテンツ      | 2023-06-20 02:02:19 |
|  2 | こんてんつ      | 2023-06-20 02:02:19 |
|  3 | ｺﾝﾃﾝﾂ           | 2023-06-20 02:02:19 |
|  4 | こんテンﾂ       | 2023-06-20 02:02:19 |
+----+-----------------+---------------------+
4 rows in set (0.01 sec)

ん？1つ目のselect SQLは、条件でcontentカラムが'コンテンツ'のレコードを指定したけど、contentカラムが平仮名の'こんてんつ'、半角カタカナの'ｺﾝﾃﾝﾂ'、平仮名全角カタカナ半角カタカナが混ざった'こんテンﾂ'まで検索結果で返ってきました。
平仮名カタカナやカタカナの半角全角が全然区別できていません。
2つ目のselect SQLも同じで区別できてないです。

カラム値が数字と英語のレコードはどうか？

> SELECT  * FROM hoge_data where content = 'hoge';
+----+---------+---------------------+
| id | content | updated_at          |
+----+---------+---------------------+
|  5 | HOGE    | 2023-06-20 02:02:19 |
|  6 | hoge    | 2023-06-20 02:02:19 |
|  7 | HOge    | 2023-06-20 02:02:19 |
+----+---------+---------------------+
3 rows in set (0.01 sec)

> SELECT  * FROM hoge_data where content = '12345';
+----+-----------------+---------------------+
| id | content         | updated_at          |
+----+-----------------+---------------------+
|  8 | 12345           | 2023-06-20 02:02:19 |
|  9 | １２３４５      | 2023-06-20 02:02:19 |
| 10 | 12３４５        | 2023-06-20 02:02:19 |
+----+-----------------+---------------------+

ん〜、英語も数字も半角全角を区別できていません。（数字(12345)の全角半角の見分けが付きにくいのは注意。）

データとして平仮名カタカナや英数字の半角全角の区別を付けたいので、このままではまずいという事で調査開始。
そしたらどうやらMySQLサーバのデータベースは、接続文字セットとCOLLATION（照合順序）というものが設定されていて、使用しているCOLLATIONによっては平仮名カタカナや英数字の半角全角を区別したりしなかったりできるという事がわかりました。

という事で、まずは現状把握で今自分が使用しているMySQLの文字セットとCOLLATIONN（照合順序）を確認します（useでDBを選択してから確認する。）

> use hogedb;
Database changed

> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4                  | utf8mb4_0900_ai_ci   |
+--------------------------+----------------------+

OS環境やMySQLのバージョンによって異なると思いますが、私のMySQL環境では接続文字セットはutf8mb4、COLLATIONはutf8mb4_0900_ai_ciに設定されていました。

COLLATIONがutf8mb4_0900_ai_ciに設定されている場合、ひらがなカタカナの区別や、英字・数字の半角全角は区別されないので、これが今回の問題の原因だとわかりました（ちなみに語尾の_ciは、Case Insensitiveです。）

■一覧でMySQLの使用可能な文字セットとデフォルトのCOLLATIONの情報を確認したい場合です。

> SHOW CHARACTER SET;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| binary   | Binary pseudo charset           | binary              |      1 |
〜
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| utf8mb3  | UTF-8 Unicode                   | utf8mb3_general_ci  |      3 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_0900_ai_ci  |      4 |
+----------+---------------------------------+---------------------+--------+

例えば、文字セットがasciiの場合、COLLATIONのデフォルトはascii_general_ciに設定されているという見方だと思います。（utf8mb4ならutf8mb4_0900_ai_ciになってる。）

SHOW COLLATIONでもCollationの一覧とデフォルト設定を確認できるので、LIKE演算子でCharsetをutf8mb4に絞って確認します。

> SHOW COLLATION WHERE Charset LIKE '%utf8mb4%';
+----------------------------+---------+-----+---------+----------+---------+---------------+
| Collation                  | Charset | Id  | Default | Compiled | Sortlen | Pad_attribute |
+----------------------------+---------+-----+---------+----------+---------+---------------+
| utf8mb4_0900_ai_ci         | utf8mb4 | 255 | Yes     | Yes      |       0 | NO PAD        |
| utf8mb4_0900_as_ci         | utf8mb4 | 305 |         | Yes      |       0 | NO PAD        |
| utf8mb4_0900_as_cs         | utf8mb4 | 278 |         | Yes      |       0 | NO PAD        |
| utf8mb4_0900_bin           | utf8mb4 | 309 |         | Yes      |       1 | NO PAD        |
| utf8mb4_bg_0900_ai_ci      | utf8mb4 | 318 |         | Yes      |       0 | NO PAD        |
〜

やはり文字セットがutf8mb4の場合、Collationのデフォルトはutf8mb4_0900_ai_ciですね。

もう少し現状把握のための調査をします。
hogedbデータベース作成時（create database〜の実行時）のスキーマを確認したら、コメントアウトされているけどデフォルトのCharasetがutf8mb4で、COLLATEがutf8mb4_0900_ai_ciとなっているのが分かります。

> show create schema hogedb\G
*************************** 1. row ***************************
       Database: hogedb
Create Database: CREATE DATABASE `hogedb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */

information_schemaデータベースのschemataテーブルでも、hogedbのデフォルト文字セットやCOLLATIONを確認できます。

> select * from information_schema.schemata where schema_name = 'hogedb'\G
*************************** 1. row ***************************
              CATALOG_NAME: def
               SCHEMA_NAME: hogedb
DEFAULT_CHARACTER_SET_NAME: utf8mb4
    DEFAULT_COLLATION_NAME: utf8mb4_0900_ai_ci
                  SQL_PATH: NULL
        DEFAULT_ENCRYPTION: NO

今度はhoge_dataテーブルを指定して、テーブルとカラム単位でスキーマを確認します。

> show full columns from hoge_data;
+------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field      | Type         | Collation          | Null | Key | Default | Extra          | Privileges                      | Comment |
+------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| id         | int          | NULL               | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| content    | varchar(512) | utf8mb4_0900_ai_ci | YES  |     | NULL    |                | select,insert,update,references |         |
| updated_at | timestamp    | NULL               | NO   |     | NULL    |                | select,insert,update,references |         |
+------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+

contentカラムのCollationはutf8mb4_0900_ai_ciに設定されています。

あと、show create tableでhoge_dataテーブルを作成した時のスキーマを確認します。

> show create table hoge_data\G
*************************** 1. row ***************************
       Table: hoge_data
Create Table: CREATE TABLE `hoge_data` (
  `id` int NOT NULL AUTO_INCREMENT,
  `content` varchar(512) DEFAULT NULL,
  `updated_at` timestamp NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

hoge_dataテーブルのCOLLATEはutf8mb4_0900_ai_ciに設定されています。
show table status from ~でも、テーブルのCollationを確認できます。

> show table status from hogedb\G
*************************** 1. row ***************************
           Name: hoge_data
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
〜
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
〜

(広告)アマゾンでSQL・DB（データベース）関連の本(Kindle)を探す！

MySQLのDB作成時にCOLLATIONを指定して、ひらがなカタカナや英数字の全角半角を区別する

ここまで色々と調べた結果、MySQLのテーブルのカラムのひらがなカタカナが区別されない、英数字の全角半角が区別されない原因は、create databaseでDB作成時のデフォルトの文字セットがutf8mb4、COLLATIONがutf8mb4_0900_ai_ciに設定されるからだと分かりました。（自分のMySQL環境の場合。環境によってデフォルト文字セットとCOLLATIONは異なると思う。）

ですので、DB作成時にひらがなカタカナや半角全角を区別するCOLLATIONを指定してDBを作成すれば良いわけです。
文字セットはデフォルトのutf8mb4のままでいいので、COLLATIONだけutf8mb4_0900_binを指定してDBを作成します。
（*utf8mb4_0900_binは、"SHOW COLLATION WHERE Charset LIKE '%utf8mb4%';"でCollationの一覧を確認して、その中から選択したので、これも各自の環境のデフォルトCOLLATIONに合わせて選択してください。）

> create database hogedb2 COLLATE utf8mb4_0900_bin;

ここではhogedb2という名前のDBを作りました。
あとは、hogedbの時と同じように、hogedb2を選択してhoge_dataテーブルとテスト用のレコードデータを作ります。

use hogedb2;
Database changed

> create table hoge_data (id integer auto_increment, content varchar(512), updated_at timestamp not null, primary key (id));

> INSERT INTO hoge_data(content, updated_at) VALUES 
('コンテンツ', LOCALTIME()),
('こんてんつ', LOCALTIME()),
('ｺﾝﾃﾝﾂ', LOCALTIME()),
('こんテンﾂ', LOCALTIME()),
('HOGE', LOCALTIME()),
('hoge', LOCALTIME()),
('HOge', LOCALTIME()),
('12345', LOCALTIME()),
('１２３４５', LOCALTIME()),
('12３４５', LOCALTIME());

テーブルとデータの準備ができたので、contentカラムを条件指定してselect SQLを実行します。

> select * from hoge_data where content = 'コンテンツ';
+----+-----------------+---------------------+
| id | content         | updated_at          |
+----+-----------------+---------------------+
|  1 | コンテンツ      | 2023-06-21 03:18:53 |
+----+-----------------+---------------------+

> SELECT * FROM hoge_data where content = 'hoge';
+----+---------+---------------------+
| id | content | updated_at          |
+----+---------+---------------------+
|  6 | hoge    | 2023-06-21 03:18:53 |
+----+---------+---------------------+

> SELECT * FROM hoge_data where content = '12345';
+----+---------+---------------------+
| id | content | updated_at          |
+----+---------+---------------------+
|  8 | 12345   | 2023-06-21 03:18:53 |
+----+---------+---------------------+

しっかり平仮名カタカナや英数字の全角半角の区別ができています！

あと、作成したDB（hogedb2）やhoge_dataテーブルのスキーマも確認してみます。
hogedb2のCOLLATIONはutf8mb4_0900_binに設定されています。

> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4                  | utf8mb4_0900_bin     |
+--------------------------+----------------------+

> show create schema hogedb2\G
*************************** 1. row ***************************
       Database: hogedb2
Create Database: CREATE DATABASE `hogedb2` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin */ /*!80016 DEFAULT ENCRYPTION='N' */

> select * from information_schema.schemata where schema_name = 'hogedb2'\G
*************************** 1. row ***************************
              CATALOG_NAME: def
               SCHEMA_NAME: hogedb2
DEFAULT_CHARACTER_SET_NAME: utf8mb4
    DEFAULT_COLLATION_NAME: utf8mb4_0900_bin
                  SQL_PATH: NULL
        DEFAULT_ENCRYPTION: NO

hoge_dataテーブルとcontentカラムのCollationもutf8mb4_0900_binに設定されています。

> show full columns from hoge_data;
+------------+--------------+------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field      | Type         | Collation        | Null | Key | Default | Extra          | Privileges                      | Comment |
+------------+--------------+------------------+------+-----+---------+----------------+---------------------------------+---------+
| id         | int          | NULL             | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| content    | varchar(512) | utf8mb4_0900_bin | YES  |     | NULL    |                | select,insert,update,references |         |
| updated_at | timestamp    | NULL             | NO   |     | NULL    |                | select,insert,update,references |         |
+------------+--------------+------------------+------+-----+---------+----------------+---------------------------------+---------+

> show create table hoge_data\G
*************************** 1. row ***************************
       Table: hoge_data
Create Table: CREATE TABLE `hoge_data` (
  `id` int NOT NULL AUTO_INCREMENT,
  `content` varchar(512) COLLATE utf8mb4_0900_bin DEFAULT NULL,
  `updated_at` timestamp NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin

> show table status from hogedb2\G
*************************** 1. row ***************************
           Name: hoge_data
         Engine: InnoDB
〜
      Collation: utf8mb4_0900_bin
〜

(広告)Amazonでコマンド操作関連のKindleや書籍を探す。コマンド操作に強くなる！

MySQLでひらがなカタカナや英数字の全角半角の区別されないDBのCOLLATIONの設定を変更して区別する

create database〜でDB作成時にCOLLATIONを指定する事で、ひらがなカタカナや英数字の全角半角の区別をできるようになりましたが、既に作成したDBが区別できていなくて区別できるようにしたい場合は、COLLATIONの設定を変更する必要があります。

最初に作成したhogedbデータベースが区別できないままなので、hogedbのCOLLATIONをutf8mb4_0900_ai_ciからutf8mb4_0900_binに変更して区別できるようにしてみます。

DBのCOLLATIONを変更するには、ALTER DATABASE COLLATE〜でできるので、hogedbにチェンジしてから実行します。

> use hogedb;
Database changed

> alter database collate utf8mb4_0900_bin;
Query OK, 1 row affected (0.09 sec)

contentカラムを条件指定してselect SQLで確認します。

> select * from hoge_data where content = 'コンテンツ';
+----+-----------------+---------------------+
| id | content         | updated_at          |
+----+-----------------+---------------------+
|  1 | コンテンツ      | 2023-06-21 02:02:39 |
|  2 | こんてんつ      | 2023-06-21 02:02:39 |
|  3 | ｺﾝﾃﾝﾂ           | 2023-06-21 02:02:39 |
|  4 | こんテンﾂ       | 2023-06-21 02:02:39 |
+----+-----------------+---------------------+

あれ？平仮名カタカナの区別ができてない。ちょっとDBのスキーマを確認してみます。

> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4                  | utf8mb4_0900_bin     |
+--------------------------+----------------------+

mysql:root@[hogedb]> show create schema hogedb\G
*************************** 1. row ***************************
       Database: hogedb
Create Database: CREATE DATABASE `hogedb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin */ /*!80016 DEFAULT ENCRYPTION='N' */

mysql:root@[hogedb]> select * from information_schema.schemata where schema_name = 'hogedb'\G
*************************** 1. row ***************************
              CATALOG_NAME: def
               SCHEMA_NAME: hogedb
DEFAULT_CHARACTER_SET_NAME: utf8mb4
    DEFAULT_COLLATION_NAME: utf8mb4_0900_bin
                  SQL_PATH: NULL
        DEFAULT_ENCRYPTION: NO

hogedbデータベースのCOLLATIONはちゃんとutf8mb4_0900_binに変更されてますね。

テーブルとカラムのスキーマも確認してみます。

> show full columns from hoge_data;
+------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field      | Type         | Collation          | Null | Key | Default | Extra          | Privileges                      | Comment |
+------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| id         | int          | NULL               | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| content    | varchar(512) | utf8mb4_0900_ai_ci | YES  |     | NULL    |                | select,insert,update,references |         |
| updated_at | timestamp    | NULL               | NO   |     | NULL    |                | select,insert,update,references |         |
+------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+

> show create table hoge_data\G
*************************** 1. row ***************************
       Table: hoge_data
Create Table: CREATE TABLE `hoge_data` (
  `id` int NOT NULL AUTO_INCREMENT,
  `content` varchar(512) DEFAULT NULL,
  `updated_at` timestamp NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

hoge_dataテーブルとcontentカラムのCollationはutf8mb4_0900_ai_ciのままですね。これだとまだダメです。

ちなみにhogedbデータベースだけCOLLATIONをutf8mb4_0900_binに変更しましたが、この状態でhogedbデータベース上で新規テーブルを作成すると、新規作成したテーブルとchar型・varchar型カラムのCollationはutf8mb4_0900_binに設定されていました。

今度はhoge_dataテーブルのCOLLATIONをutf8mb4_0900_binに変更します。

> alter table hoge_data COLLATE utf8mb4_0900_bin;

確認してみます。

> select * from hoge_data where content = 'コンテンツ';
+----+-----------------+---------------------+
| id | content         | updated_at          |
+----+-----------------+---------------------+
|  1 | コンテンツ      | 2023-06-21 02:02:39 |
|  2 | こんてんつ      | 2023-06-21 02:02:39 |
|  3 | ｺﾝﾃﾝﾂ           | 2023-06-21 02:02:39 |
|  4 | こんテンﾂ       | 2023-06-21 02:02:39 |
+----+-----------------+---------------------+

まだ区別されてない。ダメです。

再度、テーブルとカラムのスキーマを確認します。

> show create table hoge_data\G
*************************** 1. row ***************************
       Table: hoge_data
Create Table: CREATE TABLE `hoge_data` (
  `id` int NOT NULL AUTO_INCREMENT,
  `content` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `updated_at` timestamp NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin

hoge_dataテーブルのCOLLATEはutf8mb4_0900_binに変更されたけど、contentカラムはutf8mb4_0900_ai_ciのままです。

ちなみにhoge_dataテーブルのCOLLATEがutf8mb4_0900_binなら、hoge_dataテーブルに新しいchar型やvarchar型のカラムを追加した場合、そのカラムのCOLLATEはutf8mb4_0900_binになります。

今度はcontentカラムのCOLLATIONをutf8mb4_0900_binに変更します。

> alter table hoge_data modify column content varchar(512) COLLATE utf8mb4_0900_bin;

再度、確認してみます。

> select * from hoge_data where content = 'コンテンツ';
+----+-----------------+---------------------+
| id | content         | updated_at          |
+----+-----------------+---------------------+
|  1 | コンテンツ      | 2023-06-21 02:02:39 |
+----+-----------------+---------------------+

ようやく区別できるようになりました！一応スキーマも確認します。

> show create table hoge_data\G
*************************** 1. row ***************************
       Table: hoge_data
Create Table: CREATE TABLE `hoge_data` (
  `id` int NOT NULL AUTO_INCREMENT,
  `content` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin DEFAULT NULL,
  `updated_at` timestamp NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin

contentカラムのCOLLATEもutf8mb4_0900_binに変更されています。

Collationの設定管理は、DB、テーブル、カラムで別々という事ですね。
これで今回の問題は解決です。解決したけど、DBを作成して後々になって設定変更するより、やっぱりDB作成時にちゃんとCOLLATIONを指定した方が楽で良さそうです。

MySQLのSQLの条件式でCOLLATEを指定する事で、ひらがなカタカナ、英数字の全角半角を区別する

MySQLのDB・テーブル・カラムのCOLLATEを指定する事により、平仮名カタカナ、英数字の全角半角を区別したりしなかったりのスキーマ設定をできる事が解りました。

ただ、SQLの条件式でもCOLLATEを指定する事もできます。例えば、スキーマのCOLLATEがutf8mb4_0900_ai_ciで、区別しないスキーマだっとします。普通にSQLを実行するとこうなります。

> SELECT * FROM hoge_data where content = 'コンテンツ';
+----+-----------------+---------------------+
| id | content         | updated_at          |
+----+-----------------+---------------------+
|  1 | コンテンツ      | 2023-06-21 13:49:41 |
|  2 | こんてんつ      | 2023-06-21 13:49:41 |
|  3 | ｺﾝﾃﾝﾂ           | 2023-06-21 13:49:41 |
|  4 | こんテンﾂ       | 2023-06-21 13:49:41 |
+----+-----------------+---------------------+

次に、SQLの条件式でCOLLATEを指定する事で、平仮名カタカナ、英数字の全角半角が区別をできるようになります。

> SELECT * FROM hoge_data where content collate utf8mb4_0900_bin = 'コンテンツ';
+----+-----------------+---------------------+
| id | content         | updated_at          |
+----+-----------------+---------------------+
|  1 | コンテンツ      | 2023-06-21 13:49:41 |
+----+-----------------+---------------------+

> SELECT * FROM hoge_data where content collate utf8mb4_0900_bin = 'hoge';
+----+---------+---------------------+
| id | content | updated_at          |
+----+---------+---------------------+
|  6 | hoge    | 2023-06-21 13:49:41 |
+----+---------+---------------------+

> SELECT * FROM hoge_data where content collate utf8mb4_0900_bin = '12345';
+----+---------+---------------------+
| id | content | updated_at          |
+----+---------+---------------------+
|  8 | 12345   | 2023-06-21 13:49:41 |
+----+---------+---------------------+

ただ、毎回SQLの条件式でcollateを指定するのは面倒で実用的じゃないから、やはり事前にDB、テーブル、カラムのスキーマでcollateを設定しておいた方が良いですね。

参考サイト

10.2 MySQL での文字セットと照合順序
10.3.3 データベース文字セットおよび照合順序

MySQLのひらがなカタカナ、英数字の全角半角を区別する。COLLATEE(照合順序)設定

このページの目的と動作環境

MySQLのDBでひらがなカタカナやカタカナ英数字の全角半角が区別しない問題を調査。原因はCOLLATION（照合順序）の指定

MySQLのDB作成時にCOLLATIONを指定して、ひらがなカタカナや英数字の全角半角を区別する

MySQLでひらがなカタカナや英数字の全角半角の区別されないDBのCOLLATIONの設定を変更して区別する

MySQLのSQLの条件式でCOLLATEを指定する事で、ひらがなカタカナ、英数字の全角半角を区別する

参考サイト

DB・インフラ関連色々

フリーランスのITエンジニア

Python

WordPress環境構築

記事色々

(広告)アマゾンでSQL・DB（データベース）関連の本(Kindle)を探す！

Java入門ガイド