极客笔记MySQL 如何将逗号分隔的列表设置为MySQL中的表？
在数据处理过程中，我们经常会遇到将逗号分隔的数据转换成表格形式的需求，这在MySQL中也不例外。以下是一些示例代码，演示如何将逗号分隔的列表设置为MySQL中的表。
阅读更多：MySQL 教程
1. 使用MySQL的字符串函数
MySQL提供了一些字符串函数，可以方便地将逗号分隔的字符串拆分成不同的行，并将它们添加到表中。以下是一个示例：
-- 创建一个空表
CREATE TABLE mytable (id INT, name VARCHAR(50));
-- 插入逗号分隔的数据
INSERT INTO mytable
SELECT NULL, SUBSTRING_INDEX(SUBSTRING_INDEX('Alice,Bob,Charlie', ',', numbers.n), ',', -1) AS name
FROM
(SELECT @row_number:=@row_number+1 AS n
FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t2,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t3,
(SELECT @row_number:=0) AS t0) numbers
WHERE LENGTH(name) > 0;
上面的代码将会创建一个名为mytable的表，其中包含一个id列和一个name列。然后将逗号分隔的字符串'Alice,Bob,Charlie'拆分成三个字符串，并将它们插入到mytable表中。
这段代码的核心是使用了MySQL的SUBSTRING_INDEX()函数，该函数可以根据指定的分隔符从字符串中提取子串。SUBSTRING_INDEX()函数的第一个参数是要进行拆分的字符串，第二个参数是分隔符，第三个参数是要提取的子串在拆分后的结果中的位置（如果为负数，则从结果集的末尾往前数）。
2. 使用MySQL的正则表达式函数
除了字符串函数外，MySQL还提供了一些正则表达式函数，可以用于更复杂的字符串操作。以下是一个示例：
-- 创建一个空表
CREATE TABLE mytable (id INT, name VARCHAR(50));
-- 插入逗号分隔的数据
INSERT INTO mytable
SELECT NULL, REGEXP_SUBSTR('Alice,Bob,Charlie', '[^,]+', 1, numbers.n) AS name
FROM
(SELECT @row_number:=@row_number+1 AS n
FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t2,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t3,
(SELECT @row_number:=0) AS t0) numbers;
上面的代码与第一段代码类似，它也将逗号分隔的字符串'Alice,Bob,Charlie'拆分成三个字符串，并将它们插入到mytable表中。不同之处在于，这里使用了REGEXP_SUBSTR()函数来提取逗号分隔的子字符串，这个函数很类似于SUBSTRING_INDEX()函数，但是它可以使用正则表达式作为分隔符。
3. 使用Python和MySQL DB-API
如果你更喜欢使用Python编程语言来处理数据，那么你可以使用MySQL的Python客户端库，并将逗号分隔的数据作为输入，然后使用Python语言将其转换为表格格式。以下是一个示例：
import mysql.connector
# 连接MySQL数据库
cnx = mysql.connector.connect(user='username', password='password', host='localhost', database='mydatabase')
# 创建一个新表
cursor = cnx.cursor()
cursor.execute('CREATE TABLE mytable (id INT, name VARCHAR(50))')
# 将逗号分隔的数据转换成表格格式
data = 'Alice,Bob,Charlie'
names = data.split(',')
for name in names:
cursor.execute('INSERT INTO mytable VALUES (%s, %s)', (None, name))
# 提交更改并关闭连接
cnx.commit()
cursor.close()
cnx.close()
上面的代码首先使用MySQL的Python客户端库连接到MySQL数据库，并使用cursor()方法创建一个光标对象。然后，它使用execute()方法创建一个新的mytable表。接着，它将逗号分隔的字符串'Alice,Bob,Charlie'转换成一个名为names的字符串列表，并在一个for循环中将每个字符串都作为新的行插入到mytable表中。最后，它调用commit()方法提交更改，并使用close()方法关闭连接。