がるの健忘録

エンジニアでゲーマーで講師で占い師なおいちゃんのブログです。

モットゼンリョクデ アリエナイ orz

すぺしゃるさんくすゆどうふさん( id:Yudoufu )。


んとねあのねえとね。
……………MySQL使うのやめようかしらん本気で思ったぞおいら。


相変わらずの材料紹介。

create table test (
  id int,
  a varchar(100),
  b text,
  c varchar(100) binary,
  d text binary
);
insert into test(id, a,b,c,d) value(1, 'aaa', 'aaa', 'aaa', 'aaa');
insert into test(id, a,b,c,d) value(2, 'AAA', 'AAA', 'AAA', 'AAA');
insert into test(id, a,b,c,d) value(3, 'bbb', 'bbb', 'bbb', 'bbb');
insert into test(id, a,b,c,d) value(4, 'BBB', 'BBB', 'BBB', 'BBB');
insert into test(id, a,b,c,d) value(5, 'aaa  ', 'aaa  ', 'aaa  ', 'aaa  ');
insert into test(id, a,b,c,d) value(6, '  aaa', '  aaa', '  aaa', '  aaa');

5番は文字列の後ろにスペースが2つ、6番は文字列の手前にスペースが2個入ってるの。
…うん勘のいい人はこの辺で察して;;


んで。…逃げ出したくなるような結果一覧。

mysql> select * from test where a='aaa';
+------+-------+-------+-------+-------+
| id   | a     | b     | c     | d     |
+------+-------+-------+-------+-------+
|    1 | aaa   | aaa   | aaa   | aaa   |
|    2 | AAA   | AAA   | AAA   | AAA   |
|    5 | aaa   | aaa   | aaa   | aaa   |
+------+-------+-------+-------+-------+
3 rows in set (0.00 sec)

mysql> select * from test where c='aaa';
+------+-------+-------+-------+-------+
| id   | a     | b     | c     | d     |
+------+-------+-------+-------+-------+
|    1 | aaa   | aaa   | aaa   | aaa   |
|    5 | aaa   | aaa   | aaa   | aaa   |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)


mysql> select * from test where a=' aaa';
Empty set (0.00 sec)

mysql> select * from test where c=' aaa';
Empty set (0.00 sec)


mysql> select * from test where a='  aaa';
+------+-------+-------+-------+-------+
| id   | a     | b     | c     | d     |
+------+-------+-------+-------+-------+
|    6 |   aaa |   aaa |   aaa |   aaa |
+------+-------+-------+-------+-------+
1 row in set (0.00 sec)

mysql> select * from test where c='  aaa';
+------+-------+-------+-------+-------+
| id   | a     | b     | c     | d     |
+------+-------+-------+-------+-------+
|    6 |   aaa |   aaa |   aaa |   aaa |
+------+-------+-------+-------+-------+
1 row in set (0.00 sec)


mysql> select * from test where a='a aa';
Empty set (0.00 sec)

mysql> select * from test where c='a aa';
Empty set (0.00 sec)


mysql> select * from test where a='aaa ';
+------+-------+-------+-------+-------+
| id   | a     | b     | c     | d     |
+------+-------+-------+-------+-------+
|    1 | aaa   | aaa   | aaa   | aaa   |
|    2 | AAA   | AAA   | AAA   | AAA   |
|    5 | aaa   | aaa   | aaa   | aaa   |
+------+-------+-------+-------+-------+
3 rows in set (0.00 sec)

mysql> select * from test where c='aaa ';
+------+-------+-------+-------+-------+
| id   | a     | b     | c     | d     |
+------+-------+-------+-------+-------+
|    1 | aaa   | aaa   | aaa   | aaa   |
|    5 | aaa   | aaa   | aaa   | aaa   |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)


mysql> select * from test where a='aaa          ';
+------+-------+-------+-------+-------+
| id   | a     | b     | c     | d     |
+------+-------+-------+-------+-------+
|    1 | aaa   | aaa   | aaa   | aaa   |
|    2 | AAA   | AAA   | AAA   | AAA   |
|    5 | aaa   | aaa   | aaa   | aaa   |
+------+-------+-------+-------+-------+
3 rows in set (0.00 sec)

mysql> select * from test where c='aaa          ';
+------+-------+-------+-------+-------+
| id   | a     | b     | c     | d     |
+------+-------+-------+-------+-------+
|    1 | aaa   | aaa   | aaa   | aaa   |
|    5 | aaa   | aaa   | aaa   | aaa   |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)

結論。
「文字列の後ろの空白文字は存在しないことになりました」 orz
その後の追試で、タブは空白同様の扱い、改行はちゃんと文字として認識するらしいことも判明。


………MySQL使うのやめようよ運動してもいいかなぁ?
# ………信じてたのに;;