python - 根据另一个表中的列名在 MySQL 中创建表

Question

我有一个包含 ~133M 行和 16 列的表格。我想在同一服务器上的另一个数据库上为第 3-16 列中的每一列创建 14 个表(第 1 列和第 2 列是 `id` 和 `timestamp`，这将是在最后的 14 个表中，但不会有自己的表)，其中每个表都将具有原始列的名称。这可能只用 SQL 脚本来做吗？在我看来，这将是首选且最快的方式。

目前，我有一个 Python 脚本，它通过解析原始表的 CSV 转储(使用 50 行进行测试)、创建新表并添加关联值来“工作”，但是它非常慢(我估计将近 1年传输所有 133M 行，这显然是 Not Acceptable )。这是我第一次以任何身份使用 SQL，我确信我的代码可以加速，但由于我不熟悉 SQL，我不确定如何。中间的大 SQL 字符串命令是从我们代码库中的其他代码复制而来的。我已经尝试使用如下所示的事务，但它似乎对速度没有任何显着影响。

import re
import mysql.connector
import time

# option flags
debug = False  # prints out information during runtime
timing = True  # times the execution time of the program

# save start time for timing. won't be used later if timing is false
start_time = time.time()

# open file for reading
path = 'test_vaisala_sql.csv'
file = open(path, 'r')

# read in column values
column_str = file.readline().strip()
columns = re.split(',vaisala_|,', column_str)  # parse columns with regex to remove commas and vasiala_
if debug:
    print(columns)

# open connection to MySQL server
cnx = mysql.connector.connect(user='root', password='<redacted>',
                              host='127.0.0.1',
                              database='measurements')
cursor = cnx.cursor()

# create the table in the MySQL database if it doesn't already exist
for i in range(2, len(columns)):
    table_name = 'vaisala2_' + columns[i]
    sql_command = "CREATE TABLE IF NOT EXISTS " + \
                  table_name + "(`id` BIGINT(20) NOT NULL AUTO_INCREMENT, " \
                               "`timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, " \
                               "`milliseconds` BIGINT(20) NOT NULL DEFAULT '0', " \
                               "`value` varchar(255) DEFAULT NULL, " \
                               "PRIMARY KEY (`id`), " \
                               "UNIQUE KEY `milliseconds` (`milliseconds`)" \
                               "COMMENT 'Eliminates duplicate millisecond values', " \
                               "KEY `timestamp` (`timestamp`)) " \
                               "ENGINE=InnoDB DEFAULT CHARSET=utf8;"

    if debug:
        print("Creating table", table_name, "in database")

    cursor.execute(sql_command)

# read in rest of lines in CSV file
for line in file.readlines():
    cursor.execute("START TRANSACTION;")
    line = line.strip()
    values = re.split(',"|",|,', line)  # regex split along commas, or commas and quotes
    if debug:
        print(values)

    # iterate of each data column. Starts at 2 to eliminate `id` and `timestamp`
    for i in range(2, len(columns)):
        table_name = "vaisala2_" + columns[i]
        timestamp = values[1]

        # translate timestamp back to epoch time
        try:
            pattern = '%Y-%m-%d %H:%M:%S'
            epoch = int(time.mktime(time.strptime(timestamp, pattern)))
            milliseconds = epoch * 1000  # convert seconds to ms
        except ValueError:  # errors default to 0
            milliseconds = 0

        value = values[i]

        # generate SQL command to insert data into destination table
        sql_command = "INSERT IGNORE INTO {} VALUES (NULL,'{}',{},'{}');".format(table_name, timestamp,
                                                                                 milliseconds, value)
        if debug:
            print(sql_command)

        cursor.execute(sql_command)
cnx.commit()  # commits changes in destination MySQL server

# print total execution time
if timing:
    print("Completed in %s seconds" % (time.time() - start_time))

这不需要非常优化；如果机器必须运行几天才能完成，这是完全可以接受的。但是 1 年太长了。

Answer 1

您可以从 SELECT 创建一个表喜欢:

CREATE TABLE <other database name>.<column name>
             AS
             SELECT <column name>
                    FROM <original database name>.<table name>;

(用您的实际对象名称替换 <...> 或使用其他列或 WHERE 子句或...扩展它)

这也会将查询中的数据插入到新表中。这可能是最快的方法。

您可以使用目录中的动态 SQL 和信息(即 information_schema.columns )来创建 CREATE语句或手动创建它们，这很烦人，但我猜对于 14 列来说是可以接受的。