facebook twitter hatena line email

Mysql/emailカラム

提供: 初心者エンジニアの簡易メモ
移動: 案内検索

emailカラムの照合は大文字小文字を判別できるように、utf8_binを使うこと。

CREATE TABLE users1 (
    id   INTEGER PRIMARY KEY AUTO_INCREMENT,
    email  VARCHAR(16) COLLATE utf8_bin
);
 
CREATE TABLE users2 (
    id   INTEGER PRIMARY KEY AUTO_INCREMENT,
    email  VARCHAR(16) COLLATE utf8_general_ci
);
insert into users1(email) values ('hoge@gmail.com');
insert into users1(email) values ('HOGE@gmail.com');
insert into users2(email) values ('hoge@gmail.com');
insert into users2(email) values ('HOGE@gmail.com');
mysql> select * from users1 where email='hoge@gmail.com';
+----+----------------+
| id | email          |
+----+----------------+
|  1 | hoge@gmail.com |
+----+----------------+
1 row in set (0.00 sec)
mysql> select * from users2 where email='hoge@gmail.com';
+----+----------------+
| id | email          |
+----+----------------+
|  1 | hoge@gmail.com |
|  2 | HOGE@gmail.com |
+----+----------------+