作者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