MySQL中使用SELECT语句插入表名失败，请求排查原因

问题描述 #

尝试将指定数据库的表名插入dataset_names表，但执行后表未更新。相关SQL语句及Python代码如下：

目标表创建语句 #

CREATE TABLE dataset_names (id INT AUTO_INCREMENT PRIMARY KEY, dataset text);

尝试的插入SQL #

INSERT INTO dataset_names (dataset)
SELECT table_name FROM information_schema.tables
WHERE table_schema = '%s';

对应Python代码 #

dataset_name_query = """
                    INSERT INTO dataset_names (dataset) values(
                    SELECT table_name FROM information_schema.tables
                    WHERE table_schema = '%s');
                    """% (tablename)

csv_obj.read()
csv_obj.create()
cursor = createConnection()
cursor.execute(dataset_name_query)
closeCursor(cursor)

排查点及解决方法 #

• SQL语法错误：INSERT...SELECT写法不需要VALUES()包裹，你的语句混用了两种插入格式，导致语法错误。修正后的SQL应为：

  INSERT INTO dataset_names (dataset)
  SELECT table_name FROM information_schema.tables
  WHERE table_schema = '%s';
  

  对应Python代码里要删除values(和对应的闭合括号。

• 未提交事务：若数据库连接默认关闭自动提交，执行插入后需要手动提交事务。在cursor.execute()后添加：

  cursor.connection.commit()
  

  也可以在创建连接时设置autocommit=True。

• 变量替换风险与匹配问题：直接用%s字符串格式化存在SQL注入风险，且若tablename含特殊字符可能导致数据库名匹配失败。建议改用参数化查询：

  dataset_name_query = """
                      INSERT INTO dataset_names (dataset)
                      SELECT table_name FROM information_schema.tables
                      WHERE table_schema = %s;
                      """
  cursor.execute(dataset_name_query, (tablename,))
  

  注意这里%s不需要加引号，驱动会自动处理格式。

• 权限不足：检查操作所用数据库用户是否拥有SELECT访问information_schema.tables的权限，以及INSERT操作dataset_names表的权限。

• 无匹配数据：确认tablename的值和目标数据库名称完全一致（MySQL数据库名是否区分大小写取决于系统配置），可单独执行SELECT table_name FROM information_schema.tables WHERE table_schema = '目标数据库名';验证是否有返回结果。

内容的提问来源于stack exchange，提问作者Giovanni Michel