背景
TLDR;
- MySQL初心者の自分が最近、MySQLを使うシステムの開発を引き継いだ
- そのシステムで扱うデータは「IDの大文字と小文字を区別しない」仕様だった
- 「大文字と小文字を区別しない」をどう実装しているのか調べたら、そもそもMySQLのchar/varchar/text型はデフォルトでは大文字小文字を区別しない仕様で、そのまま使っているだけだった
- このシステムでは外部システムとの連携ができないデータがあり不具合になっていた
- 調査したところ、連携できていないデータのIDには大文字が混ざっていた
- Javaのコードの方は大文字小文字を区別していたので、MySQLの照合処理と矛盾して不具合になっていた
気づき
- **MySQLはテキストデータ（char/varchar/text）がケースセンシティブじゃない**んだ！！
- 今までPostgreSQLを使っていたけれどケースセンシティブだったし、ほかのRDBMSも同じだと思い込んでいた。
（ケースセンシティブ = 大文字小文字を区別する）
疑問
ほかのRDBMSのテキストデータはケースセンシティブなのか？
調査結果
| RDBMS | ケースセンシティブである | 設定で変えられる |
|---|---|---|
| Oracle | YES | YES |
| PostgreSQL | YES | YES |
| MySQL | NO | YES |
| H2 | YES | 未調査 |
調査方法
- 【登録】文字列型のprimary keyに大文字と小文字のデータをそれぞれ挿入してみる
- ケースセンシティブなら両者は違う値となり挿入できる
- ケースセンシティブでないなら2つは同値となり重複エラーで挿入できない
- 【検索】文字列型のカラムを大文字と小文字の両方で検索してみる
- ケースセンシティブなら大文字は大文字、小文字は小文字にしかヒットしない
- ケースセンシティブでないならどちらの検索でもデータがヒットする
Oracle（バージョン 11gR2）
結論
Oracleはテキストデータの大文字と小文字を区別する。
事前準備
CREATE TABLE students
(
id VARCHAR2(10),
name VARCHAR2(50),
CONSTRAINT pk1 PRIMARY KEY(id)
);
1. 登録テスト
大文字と小文字を別データとして登録できた。
insert into students(id, name) values('AAAAA', 'John Smith');
insert into students(id, name) values('aaaaa', 'John Smith');
select * from students;
| ID | NAME |
|---|---|
| AAAAA | John Smith |
| aaaaa | John Smith |
2. 検索テスト
大文字と小文字を別データとして検索できた。
select * from students where id = 'AAAAA';
| ID | NAME |
|---|---|
| AAAAA | John Smith |
select * from students where id = 'aaaaa';
| ID | NAME |
|---|---|
| aaaaa | John Smith |
設定
大文字小文字を区別しないようにも設定できる。
照合BINARY_CIでは、大/小文字のみ異なる文字値は同じ値として処理されます。
docs.oracle.com
PostgreSQL（バージョン 9.6）
結論
PostgreSQLはテキストデータの大文字と小文字を区別する。
事前準備
CREATE TABLE students (
id VARCHAR(10),
name VARCHAR(50),
PRIMARY KEY (id)
);
```
### 1. 登録テスト
大文字と小文字を別データとして登録できた。
```sql
insert into students(id, name) values('AAAAA', 'John Smith');
insert into students(id, name) values('aaaaa', 'John Smith');
select * from students;
```
| ID | NAME |
|---|---|
| AAAAA | John Smith |
| aaaaa | John Smith |
### 2. 検索テスト
大文字と小文字を別データとして検索できた。
```sql
select * from students where id = 'AAAAA';
```
| ID | NAME |
|---|---|
| AAAAA | John Smith |
```sql
select * from students where id = 'aaaaa';
```
| ID | NAME |
|---|---|
| aaaaa | John Smith |
### 設定
大文字小文字を区別しないようにも設定できる。
> citextモジュールは、大文字小文字の区別がない文字列型を提供します。 これは値の比較の際、基本的に内部的でlowerを呼び出します。 この他はほぼtextと同様に動作します。
> [postgresql.jp](https://www.postgresql.jp/document/9.6/html/citext.html)
## MySQL（バージョン 5.6）
### 結論
MySQLはテキストデータの大文字と小文字を**区別しない**。
### 事前準備
```sql
CREATE TABLE students (
id VARCHAR(10),
name VARCHAR(50),
PRIMARY KEY ( id )
);
```
### 1. 登録テスト
大文字と小文字を別データとして登録できなかった。
```sql
insert into students(id, name) values('AAAAA', 'John Smith');
insert into students(id, name) values('aaaaa', 'John Smith');
```
エラー
```
Duplicate entry 'aaaaa' for key 'PRIMARY'
```
違う小文字のデータを登録した。
```sql
insert into students(id, name) values('bbbbb', 'Jane Brown');
select * from students;
```
| ID | NAME |
|---|---|
| AAAAA | John Smith |
| bbbbb | Jane Brown |
### 2. 検索テスト
大文字と小文字を別データとして検索できなかった。
```sql
select * from students where id = 'aaaaa';
```
| ID | NAME |
|---|---|
| AAAAA | John Smith |
```sql
select * from students where id = 'BBBBB';
```
| ID | NAME |
|---|---|
| bbbbb | Jane Brown |
### 設定
大文字小文字を区別するようにも設定できる。
- 検索するときだけケースセンシティブにするには検索句に `BINARY` を付ける
- `WHERE BINARY id = 'aaaaa' AND name = 'John Smith'` はすべての条件でケースセンシティブな検索をする
- `WHERE id BINARY = 'aaaaa' AND name = 'John Smith'` はidカラムだけケースセンシティブな検索をする
- カラムの設定をケースセンシティブに変えてしまうには型の後ろに `BINARY` を付ける
カラムの設定をケースセンシティブに変えてみた。
```sql
ALTER TABLE students MODIFY id VARCHAR(10) BINARY;
insert into students(id, name) values('aaaaa', 'John Smith');
select * from students;
```
| ID | NAME |
|---|---|
| AAAAA | John Smith |
| bbbbb | Jane Brown |
| **aaaaa** | **John Smith** |
大文字で検索してみると小文字はヒットしない。
```sql
select * from students where id = 'AAAAA';
```
| ID | NAME |
|---|---|
| AAAAA | John Smith |
```sql
select * from students where id = 'BBBBB';
```
| ID | NAME |
|---|---|
小文字で検索してみると大文字はヒットしない。
```sql
select * from students where id = 'aaaaa';
```
| ID | NAME |
|---|---|
| aaaaa | John Smith |
## H2（バージョン 2.0.206）
### 結論
H2はテキストデータの大文字と小文字を区別する。
### 事前準備
```sql
CREATE TABLE students (
id VARCHAR(10),
name VARCHAR(50),
PRIMARY KEY ( id )
);
```
### 1. 登録テスト
大文字と小文字を別データとして登録できた。
```sql
insert into students(id, name) values('AAAAA', 'John Smith');
insert into students(id, name) values('aaaaa', 'John Smith');
select * from students;
```
| ID | NAME |
|---|---|
| AAAAA | John Smith |
| aaaaa | John Smith |
### 2. 検索テスト
大文字と小文字を別データとして検索できた。
```sql
select * from students where id = 'AAAAA';
```
| ID | NAME |
|---|---|
| AAAAA | John Smith |
```sql
select * from students where id = 'aaaaa';
```
| ID | NAME |
|---|---|
| aaaaa | John Smith |
### 設定
未調査