大家好，
今天處理一個欄目的資訊分為四個新欄目來處理：
+--------------------------------+
| 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
→
04/16 13:51, , 1F
04/16 13:51, 1F
→
04/16 13:52, , 2F
04/16 13:52, 2F
→
04/16 13:54, , 3F
04/16 13:54, 3F
→
04/16 13:55, , 4F
04/16 13:55, 4F
→
04/16 17:50, , 5F
04/16 17:50, 5F
→
04/16 17:50, , 6F
04/16 17:50, 6F
→
04/17 10:52, , 7F
04/17 10:52, 7F