在不使用SQL\u安全更新的情况下更新表

2024-05-08 23:15:55 发布

您现在位置:Python中文网/ 问答频道 /正文

我正在尝试使用此查询更新一些记录,这基本上只是一种为客户设置“状态”的方法,考虑到他们的“自上次订单以来的天数”:

        UPDATE customers AS c1
        INNER JOIN (
          SELECT id, DATEDIFF(NOW(), agg.cdt) AS acdt
          FROM customers
          INNER JOIN
            (
              SELECT c.id AS cid, max(o.datetime) as cdt
              FROM customers AS c
              LEFT JOIN orders o ON o.customer_id = c.id
              WHERE o.state = 20
              GROUP BY c.id
            ) AS agg ON customers.id = agg.cid
          WHERE account_type IN (1, 2)
            AND deleted = 0
            AND (account_management_state IN (0, 1, 2) OR account_management_state IS NULL)
        ) AS c2 ON c1.id = c2.id
        SET c1.account_management_state = CASE
          WHEN c2.acdt <= 90 THEN 0
          WHEN c2.acdt >= 91 AND c2.acdt <= 360 THEN 1
          WHEN c2.acdt > 360 OR c2.acdt IS NULL THEN 2
          END
        WHERE c1.id = c2.id;

但我得到了:

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

我在最后的WHERE中使用表键,c1.idcustomers表键。使用SET SQL_SAFE_UPDATES = 0;不是一个选项。我还尝试使用WHERE c1.id > 0但没有效果

请注意,我试图手动运行查询来更改SET SQL_SAFE_UPDATES = 0;,查询按预期运行,但这应该是一个自动过程

因此,我的选择是:

  1. 使用单个查询,SQL_SAFE_UPDATES无法使用
  2. 使用Python游标,可以使用SQL_SAFE_UPDATES。(参见侧注)
  3. [在此插入一个选项]
  4. (我不想这样做)迭代每个记录(使用Python ORM)并更新记录。这是愚蠢的,而且要花很长时间

更新

还尝试:

  • 使用相同的ID

    UPDATE
      ...
    WHERE c1.id = c1.id ;
    
  • 在每个子查询之后添加一个巨大的限制(如@Akina所建议的):

    UPDATE customers AS c1
    INNER JOIN (
      SELECT id, DATEDIFF(NOW(), agg.cdt) AS acdt
      FROM customers
      INNER JOIN
        (
          SELECT c.id AS cid, max(o.datetime) as cdt
          FROM customers AS c
          LEFT JOIN orders o ON o.customer_id = c.id
          WHERE o.state = 20
          GROUP BY c.id
          LIMIT 100000000
        ) AS agg ON customers.id = agg.cid
      WHERE account_type IN (1, 2)
        AND deleted = 0
        AND (account_management_state IN (0, 1, 2) OR account_management_state IS NULL)
      LIMIT 100000000
    ) AS c2 ON c1.id = c2.id
    SET c1.account_management_state = CASE
      WHEN c2.acdt <= 90 THEN 0
      WHEN c2.acdt >= 91 AND c2.acdt <= 360 THEN 1
      WHEN c2.acdt > 360 OR c2.acdt IS NULL THEN 2
      END
    WHERE c1.id = c2.id;
    
  • 两个ID的组合:

    UPDATE
      ...
    WHERE c1.id > 0 and c2.id > 0;
    

他们都没有工作。仍然得到Error Code: 1175

旁注

这是使用MySQL Python客户端和游标的Python/Flask过程的一部分。我可以使用SQL_SAFE_UPDATES,只要它是从Python游标完成的。这不起作用:

  • 使用不同的查询(不会抛出错误,只是不会更新任何内容):

    connection = db.get_conn()
    cursor = connection.cursor()
    cursor.execute('SET SQL_SAFE_UPDATES = 0;')
    cursor.execute(query) # from the original query
    cursor.execute('SET SQL_SAFE_UPDATES = 1;')
    
  • 使用单个查询(不抛出错误,它不会更新任何内容):

    connection = db.get_conn()
    cursor = connection.cursor()
    cursor.execute('''
        SET SQL_SAFE_UPDATES = 0;
        UPDATE ...;
        SET SQL_SAFE_UPDATES = 1;
    ''')
    
  • 使用BEGIN .. END(我以为我看到了光明,但没有),获得ProgrammingError

    connection = db.get_conn()
    cursor = connection.cursor()
    cursor.execute('''
        BEGIN
            SET SQL_SAFE_UPDATES = 0;
            UPDATE ...;
            SET SQL_SAFE_UPDATES = 1;
        END
    ''')
    

Tags: idsqlasupdateaccountwherecursorsafe
1条回答
网友
1楼 · 发布于 2024-05-08 23:15:55

我知道这已经有很长时间了,但是今天可能和你我有同样的问题。 我已通过在关闭连接之前提交更改来解决此问题 只需在关闭连接“cnx.commit()之前添加此项” 希望这能有所帮助

相关问题 更多 >