Subscribed unsubscribe Subscribe Subscribe

SETのvaluesに空文字が含まれていると

MySQL
mysql> CREATE TABLE hoge (id INTEGER PRIMARY KEY AUTO_INCREMENT, s SET('foo', 'bar', '') NOT NULL DEFAULT '');
Query OK, 0 rows affected (0.02 sec)

mysql> DESCRIBE hoge;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | int(11)             | NO   | PRI | NULL    | auto_increment |
| s     | set('foo','bar','') | NO   |     |         |                |
+-------+---------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> INSERT INTO hoge VALUES ();
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO hoge (s) VALUES (''), (','), ('foo'), ('foo,');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM hoge;
+----+------+
| id | s    |
+----+------+
|  1 |      |
|  2 |      |
|  3 |      |
|  4 | foo  |
|  5 | foo, |
+----+------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM hoge WHERE s = '';
+----+---+
| id | s |
+----+---+
|  1 |   |
|  2 |   |
|  3 |   |
+----+---+
3 rows in set (0.00 sec)

mysql> SELECT * FROM hoge WHERE FIND_IN_SET('', s);
+----+------+
| id | s    |
+----+------+
|  3 |      |
|  5 | foo, |
+----+------+
2 rows in set (0.00 sec)

DEFAULT VALUEとして入るものや明示的に空文字を指定した場合はFIND_IN_SETでは検出されず、でもカンマを含めて入力したものは「空文字というvalueを含むSET値」として認識される。
見た目では分からないけども。

数値として見てみるとよく分かるようだ。
MySQL :: MySQL 5.6 Reference Manual :: 11.4.5 The SET Type

mysql> SELECT *, s + 0 FROM hoge;
+----+------+-------+
| id | s    | s + 0 |
+----+------+-------+
|  1 |      |     0 |
|  2 |      |     0 |
|  3 |      |     4 |
|  4 | foo  |     1 |
|  5 | foo, |     5 |
+----+------+-------+
5 rows in set (0.00 sec)

mysql> SELECT *, s + 0 FROM hoge WHERE s = 0;
+----+---+-------+
| id | s | s + 0 |
+----+---+-------+
|  1 |   |     0 |
|  2 |   |     0 |
+----+---+-------+
2 rows in set (0.00 sec)

mysql> SELECT *, s + 0 FROM hoge WHERE s & 4;
+----+------+-------+
| id | s    | s + 0 |
+----+------+-------+
|  3 |      |     4 |
|  5 | foo, |     5 |
+----+------+-------+
2 rows in set (0.00 sec)

なるほどー。
スキーマでDEFAULT値を指定するのも、","で入れておくと「空文字というvalue」がデフォルトになるようだ。

mysql> DROP TABLE hoge;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE hoge (id INTEGER PRIMARY KEY AUTO_INCREMENT, s SET('foo', 'bar', '') NOT NULL DEFAULT ',');
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW CREATE TABLE hoge\G
*************************** 1. row ***************************
       Table: hoge
Create Table: CREATE TABLE `hoge` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `s` set('foo','bar','') NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> DESCRIBE hoge;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | int(11)             | NO   | PRI | NULL    | auto_increment |
| s     | set('foo','bar','') | NO   |     |         |                |
+-------+---------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> INSERT INTO hoge VALUES ();
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO hoge (s) VALUES (''), (',');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT *, s + 0 FROM hoge;
+----+---+-------+
| id | s | s + 0 |
+----+---+-------+
|  1 |   |     4 |
|  2 |   |     0 |
|  3 |   |     4 |
+----+---+-------+
3 rows in set (0.00 sec)

mysql> SELECT *, s + 0 FROM hoge WHERE FIND_IN_SET('', s);
+----+---+-------+
| id | s | s + 0 |
+----+---+-------+
|  1 |   |     4 |
|  3 |   |     4 |
+----+---+-------+
2 rows in set (0.00 sec)

見た目じゃ全然わからんw