如何用Google Sheets列逐行更新MySQL表Winners的Winner列

问题描述 #

我有一张名为Winners的MySQL表，同时在Google Sheets里有一张表格，想把Google Sheets中LNW!P5:P35区域的内容更新到MySQL表的Winner列里。现在用Apps Script写了JDBC连接的函数，但运行后发现所有行的值都更新到MySQL的同一行里了，想知道怎么拆分区域值并逐行更新，原代码如下：

function UpdateDB(e) {

  ss = SpreadsheetApp.getActiveSpreadsheet();
  sheet = ss.getSheetByName("LNW");
  winners = sheet.getRange("P5:P35").getValues() ;

  server = "give.herosite.pro";
  port = '3306';
  dbName =  "oncjsznl_wp921";
  username = "oncjsznl_wp921";
  password = "xxxxxxxx";
  url = "jdbc:mysql://" + server + ":" + port + "/" + dbName + "?characterEncoding=UTF-8";

  conn = Jdbc.getConnection(url, username, password);
  stmt = conn.createStatement();
  stmt.execute("UPDATE `Winners` SET `Winner`='" + winners + "' ;");

  conn.close();
}

问题分析 #

原代码直接把整个winners二维数组拼到SQL语句中，数组会被转为逗号分隔的字符串，导致所有内容被一次性写入MySQL的所有行（或者同一行，取决于表的匹配规则）。要实现逐行对应更新，需要遍历数组，同时确保每一条更新语句匹配MySQL表的对应行（需依赖表中的唯一标识字段，比如自增id）。

修改后的代码 #

假设MySQL的Winners表有自增id字段，且Google Sheets的P5对应MySQL的id=1，P6对应id=2，以此类推（若匹配规则不同，可调整id的计算逻辑）：

function UpdateDB(e) {
  // 获取Google Sheets数据
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("LNW");
  const winners = sheet.getRange("P5:P35").getValues();

  // 数据库连接信息
  const server = "give.herosite.pro";
  const port = '3306';
  const dbName = "oncjsznl_wp921";
  const username = "oncjsznl_wp921";
  const password = "xxxxxxxx";
  const url = `jdbc:mysql://${server}:${port}/${dbName}?characterEncoding=UTF-8`;

  // 建立数据库连接
  const conn = Jdbc.getConnection(url, username, password);
  // 使用PreparedStatement防SQL注入，同时支持批量更新
  const stmt = conn.prepareStatement("UPDATE `Winners` SET `Winner` = ? WHERE `id` = ?");

  try {
    // 开启事务，保证批量更新的一致性
    conn.setAutoCommit(false);
    
    // 遍历每一行数据
    winners.forEach((row, index) => {
      const winnerValue = row[0]; // 取出单元格值（getValues返回二维数组，每个row是单元素数组）
      const id = index + 1; // 对应MySQL的自增id，根据实际规则调整
      
      if (winnerValue) { // 跳过空单元格
        stmt.setString(1, winnerValue);
        stmt.setInt(2, id);
        stmt.addBatch(); // 加入批量更新队列
      }
    });

    // 执行所有批量更新
    stmt.executeBatch();
    // 提交事务
    conn.commit();
  } catch (error) {
    // 出错时回滚事务，避免数据不一致
    conn.rollback();
    throw error; // 抛出错误方便调试
  } finally {
    // 关闭资源
    stmt.close();
    conn.close();
  }
}

关键说明 #

• 数组遍历：用forEach循环处理winners二维数组，通过row[0]提取单元格的实际值。
• 行匹配逻辑：这里假设MySQL表的id从1开始，对应Google Sheets的P5为第一条数据，若你的表用其他字段匹配（比如name、serial），只需修改WHERE子句和对应的参数设置。
• PreparedStatement：替代直接拼接SQL字符串，避免SQL注入风险，同时批量更新比单条执行效率更高。
• 事务处理：开启事务后，只有所有更新操作成功才提交，出错则回滚，保证数据完整性。
• 空值过滤：跳过空单元格，避免将无效空值写入数据库，可根据需求删除该判断。

内容的提问来源于stack exchange，提问作者Prabhu Paul