基于另一个表中的列的名称在MySQL中创建表
我有一个大约有133M行和16列的表。我想在同一服务器上的另一个数据库上为3-16列中的每一列创建14个表(列1和列2是`id`和`timestamp`，它们也将出现在最后14个表中，但不会有自己的表)，其中每个表都将具有原始列的名称。只使用SQL脚本可以做到这一点吗？在我看来，这是最好的，也是最快的方式。
目前，我有一个Python脚本，它通过解析原始表的CSV转储(用50行进行测试)、创建新表并添加相关的值来“工作”，但它非常慢(我估计几乎需要1年时间才能传输全部133M行，这显然是不可接受的)。这是我第一次在任何情况下使用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))这不需要进行令人难以置信的优化；如果机器必须运行几天才能做到这一点，这是完全可以接受的。但是一年太长了。
回答 2
Stack Overflow用户
发布于 2018-05-31 07:59:11
您可以从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列来说，这是可以接受的。
Stack Overflow用户
发布于 2018-05-31 08:02:46
当使用脚本与数据库对话时，您希望最小化发送的消息数量，因为每条消息都会进一步延迟您的执行时间。目前，看起来您似乎正在发送(根据您的近似值)1.33亿条消息，因此，您的脚本速度会减慢1.33亿倍。一种简单的优化方法是解析电子表格并将数据拆分到表中(在内存中或保存到磁盘中)，然后才将数据发送到新的数据库。
正如您所暗示的，编写SQL脚本来重新分发数据要快得多。
https://stackoverflow.com/questions/50614758
复制相似问题