MYSQLの大文字/小文字の扱いについての確認


例えば、MYSQLのパスワードを格納している”mysql.user”テーブルのpassword列は
大文字小文字を区別する為に以下のようにbinを指定している。

`Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT ”

case_s

——————————————————————————————
MYSQLで何も指定しないでテーブルを作成した場合。”CASE IN SENSITIVE”
——————————————————————————————
mysql> CREATE TABLE T_varchar (MOJI varchar(10) unique);
Query OK, 0 rows affected (0.17 sec)

mysql> insert into T_varchar values(‘varchar(10)’);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into T_varchar values(‘Varchar(10)’);
ERROR 1062 (23000): Duplicate entry ‘Varchar(10’ for key ‘MOJI’

mysql> select * from T_varchar;
+————+
| MOJI |
+————+
| varchar(10 |
+————+
1 row in set (0.00 sec)

——————————————————————————————
↓↓↓↓↓ MYSQLでbinaryを指定してテーブルを作成した場合  ”CASE SENSITIVE” ↓↓↓
——————————————————————————————

mysql> CREATE TABLE T_binary (MOJI binary unique);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into T_binary values(‘b’);
Query OK, 1 row affected, 1 warning (0.14 sec)

mysql> insert into T_binary values(‘b’);
ERROR 1062 (23000): Duplicate entry ‘b’ for key ‘MOJI’
mysql> select * from T_binary;
+——+
| MOJI |
+——+
| b |
+——+
1 row in set (0.01 sec)

mysql>

mysql> insert into T_binary values(‘B’);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from T_binary;
+——+
| MOJI |
+——+
| B |
| b |
+——+
2 rows in set (0.00 sec)

mysql>

——————————————————————————————

mysql> CREATE TABLE T_latin1_bin (MOJI char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT ” unique);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into T_latin1_bin values(‘T_latin1_bin’);
Query OK, 1 row affected (0.00 sec)

mysql> insert into T_latin1_bin values(‘T_latin1_bin’);
ERROR 1062 (23000): Duplicate entry ‘T_latin1_bin’ for key ‘MOJI’
mysql> select * from T_latin1_bin;
+————–+
| MOJI |
+————–+
| T_latin1_bin |
+————–+
1 row in set (0.00 sec)

mysql>

mysql> insert into T_latin1_bin values(‘t_latin1_bin’);
Query OK, 1 row affected (0.00 sec)

mysql> insert into T_latin1_bin values(‘T_latin2_bin’);
Query OK, 1 row affected (0.00 sec)

mysql> select * from T_latin1_bin;
+————–+
| MOJI |
+————–+
| T_latin1_bin |
| T_latin2_bin |
| t_latin1_bin |
+————–+
3 rows in set (0.00 sec)

mysql>

——————————————————————————————

mysql> CREATE TABLE T_utf8_bin (MOJI char(41) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ” unique);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into T_utf8_bin values(‘utf8 COLLATE utf8_bin’);
Query OK, 1 row affected (0.01 sec)

mysql> insert into T_utf8_bin values(‘utf8 COLLATE utf8_bin’);
ERROR 1062 (23000): Duplicate entry ‘utf8 COLLATE utf8_bin’ for key ‘MOJI’
mysql> select * from T_utf8_bin;
+———————–+
| MOJI |
+———————–+
| utf8 COLLATE utf8_bin |
+———————–+
1 row in set (0.00 sec)

mysql> insert into T_utf8_bin values(‘utf8 COLLATE utf8_biN’);
Query OK, 1 row affected (0.00 sec)

mysql> insert into T_utf8_bin values(‘Utf8 COLLATE utf8_bin’);
Query OK, 1 row affected (0.00 sec)

mysql> select * from T_utf8_bin;
+———————–+
| MOJI |
+———————–+
| Utf8 COLLATE utf8_bin |
| utf8 COLLATE utf8_biN |
| utf8 COLLATE utf8_bin |
+———————–+
3 rows in set (0.00 sec)

mysql>

binaryを指定してあるので、case-sensitiveなので大文字、小文字は別データとして扱われる。

utf8_bin_casesensitive

Comments are closed.

Post Navigation