MySQLでテキストデータの大文字小文字を区別しない主キーにハマったので各種RDBMSの仕様も調査した

## 背景

TLDR;

- MySQL初心者の自分が最近、MySQLを使うシステムの開発を引き継いだ
- そのシステムで扱うデータは「IDの大文字と小文字を区別しない」仕様だった
- 「大文字と小文字を区別しない」をどう実装しているのか調べたら、そもそもMySQLのchar/varchar/text型はデフォルトでは大文字小文字を区別しない仕様で、そのまま使っているだけだった
- このシステムでは外部システムとの連携ができないデータがあり不具合になっていた
- 調査したところ、連携できていないデータのIDには大文字が混ざっていた
- Javaのコードの方は大文字小文字を区別していたので、MySQLの照合処理と矛盾して不具合になっていた

## 気づき

- **[MySQLはテキストデータ（char/varchar/text）がケースセンシティブじゃない](https://dev.mysql.com/doc/refman/5.6/ja/case-sensitivity.html)**んだ！！
- 今までPostgreSQLを使っていたけれどケースセンシティブだったし、ほかのRDBMSも同じだと思い込んでいた。

（ケースセンシティブ = 大文字小文字を区別する）

## 疑問

ほかのRDBMSのテキストデータはケースセンシティブなのか？

## 調査結果

| RDBMS | ケースセンシティブである | 設定で変えられる |
|:-----:|:-----:|:-----:|
| Oracle | YES | YES |
| PostgreSQL | YES | YES |
| MySQL | **NO** | YES |
| H2 | YES | 未調査 |

## 調査方法

1. 【登録】文字列型のprimary keyに大文字と小文字のデータをそれぞれ挿入してみる
  - ケースセンシティブなら両者は違う値となり挿入できる
  - ケースセンシティブでないなら2つは同値となり重複エラーで挿入できない
2. 【検索】文字列型のカラムを大文字と小文字の両方で検索してみる 
  - ケースセンシティブなら大文字は大文字、小文字は小文字にしかヒットしない
  - ケースセンシティブでないならどちらの検索でもデータがヒットする

## Oracle（バージョン 11gR2）

### 結論

Oracleはテキストデータの大文字と小文字を区別する。

### 事前準備

```sql
CREATE TABLE students
(
    id VARCHAR2(10),
    name VARCHAR2(50),
    CONSTRAINT pk1 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 |

### 設定

大文字小文字を区別しないようにも設定できる。

> 照合BINARY_CIでは、大/小文字のみ異なる文字値は同じ値として処理されます。
> [docs.oracle.com](https://docs.oracle.com/cd/F19136_01/sqlrf/constraint.html#GUID-1055EA97-BA6F-4764-A15F-1024FD5B6DFE)


## PostgreSQL（バージョン 9.6）

### 結論

PostgreSQLはテキストデータの大文字と小文字を区別する。

### 事前準備

````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 |


### 設定

大文字小文字を区別しないようにも設定できる。

> 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 |


### 設定

未調査