作者scorsese ()
看板Database
标题[SQL ]将一个栏位中多项资讯分别放在新栏位问题
时间Mon Apr 15 14:57:12 2013
大家好，
今天处理一个栏目的资讯分为四个新栏目来处理：
+--------------------------------+
| interests |
+--------------------------------+
| women |
| sailing, fishing, yachting |
| fishing, drinking |
| RPG |
| collecting books, scuba diving |
| cooking |
| acting, dancing |
| RPG, anime |
| poetry, screenwriting |
+--------------------------------+
变成：
+------------------+---------------+-----------+-----------+
| interest1 | interest2 | interest3 | interest4 |
+------------------+---------------+-----------+-----------+
| women | | | |
| sailing | fishing | yachting | |
| fishing | drinking | | |
| RPG | | | |
| collecting books | scuba diving | | |
| cooking | | | |
| acting | dancing | | |
| RPG | anime | | |
| poetry | screenwriting | | |
+------------------+---------------+-----------+-----------+
这段变化我做的指令如下：
ALTER TABLE my_contacts
ADD COLUMN interest1 VARCHAR(50),
ADD COLUMN interest2 VARCHAR(50),
ADD COLUMN interest3 VARCHAR(50),
ADD COLUMN interest4 VARCHAR(50);
UPDATE my_contacts
SET interest1 = SUBSTRING_INDEX(interests, ',', 1);
UPDATE my_contacts
SET interests =
TRIM(RIGHT(interests, (LENGTH(interests)-LENGTH(interest1)-1)));
UPDATE my_contacts
SET interest2 = SUBSTRING_INDEX(interests, ',', 1);
UPDATE my_contacts
SET interests =
TRIM(RIGHT(interests, (LENGTH(interests)-LENGTH(interest2)-1)));
UPDATE my_contacts
SET interest3 = SUBSTRING_INDEX(interests, ',', 1);
UPDATE my_contacts
SET interests =
TRIM(RIGHT(interests, (LENGTH(interests)-LENGTH(interest3)-1)));
UPDATE my_contacts
SET interest4 = interests;
最後再把interests栏位DROP处理掉。
请问这样处理的结果，空白栏是否应该是NULL?
因为似乎我这样的处理结果出现的是空白值，而非NULL
且不知道这样的情形，不是NULL会不会有影响？
先谢谢大家回答！
--
※ 发信站: 批踢踢实业坊(ptt.cc)
◆ From: 140.112.25.108
1F：→ arkcsl:如果没有资料，应该是要显示NULL而不是'' 04/16 13:51
2F：→ arkcsl:如果是我，我会用NULL把全部的''都update掉。 04/16 13:52
3F：→ arkcsl:因为当你要还原你原本的句子的时候，你会需要用NULL来判断 04/16 13:54
4F：→ arkcsl:当然你也可以用length来判断就是.. 04/16 13:55
5F：→ scorsese:谢谢楼上，我想也是该维持NULL，只是我想怎麽这样跑的结 04/16 17:50
6F：→ scorsese:不是NULL呢？ 04/16 17:50
7F：→ arkcsl:set var = NULL where var ='' ? 04/17 10:52