load data infile separating words by "_"

I'm curren开发者_JAVA百科tly importing a dictionary in mysql which has words that are seperated by _. I wanted to know how to specify the words are seperated by _. For example the words are as such:

Super_Mario
Stack_Overflow
Another_Word

so each row would then be stored as :

Super Mario
Stack Overflow
Another Word

I have this query right now:

LOAD DATA LOCAL INFILE 
C:/upload/dictionary.csv
INTO TABLE dictionary
fields terminated by ',' lines terminated by '\n'

would I have to use fields terminated by '_'?

---

No, you just use the SET clause (just like in an UPDATE) to set the field's value with the result of a string REPLACE() operation that replaces underscores with spaces.

LOAD DATA LOCAL INFILE 
C:/upload/dictionary.csv
INTO TABLE dictionary (@var1)
SET your_column_name = REPLACE(@var1, '_', ' ')

The (@var1) bit after INTO TABLE dictionary just means "there's only one column in the file I'm interested in, and I want to store it in @var1 so I can use it later in my SET clause instead of putting it directly into a column." Do a Ctrl+F in for "SET clause" in the documentation for LOAD DATA INFILE to see how to use a SET clause when your input file has more than one column.

---

fields terminated by '_' will interpret every word separated by _ as a new column

so Super_Mario, Stack_Overflow and Another_Word would each end up as two columns in each row. If your entire dictionary is made up of two words and your dictionary table has two columns, it'll work, but I have the feeling not every word in your file is going to be two words

If you want to store each line in the file as a single column, but with all the _s replaced with spaces, you could do something like this after the import (or do what @Jordan said and do it as part of the import which sounds better to me)

UPDATE dictionary SET columnname = replace(columnname,'_',' ')