Split string into row

问题:

I am using MySQL.

I have a table named "class", the "class" table has three columns name , year and class_code like following:

Class table:

Now I would like to use the above table to create a new table named "temp", which contains class_code and value columns.

The rule is that each string value in each column field of a row of the above "class" table will be split into words, and each word will be inserted to the "temp" table as value column of a temp table record like following:

temp table:

I am using MySQL.

Is it possible to generate the "temp" table purely by using SQL statement and how?

That's :

CREATE TABLE temp;

ALTER TABLE temp DISABLE KEYS;

INSERT INTO ...(how to split the string value of each field in "class" table and insert to "temp" table??? )

P.S.: I used a simple equal length string as a value, but the actually case has very random length string, and the number of words in each string is random also.

回答1:

CREATE TABLE new_table
SELECT SUBSTRING(name,  1, 4) as field, class_code FROM old_table
UNION
SELECT SUBSTRING(name,  6, 4) as field, class_code FROM old_table
UNION
SELECT SUBSTRING(name, 11, 4) as field, class_code FROM old_table
UNION
SELECT SUBSTRING(year,  1, 4) as field, class_code FROM old_table
UNION
SELECT SUBSTRING(year,  6, 4) as field, class_code FROM old_table

回答2:

You have to write a function for string splitting in MySql as

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

And use it to split the values and do the assigning the splitted values and inserting in to table in a SP.For more information refer to the MySQL Split String