Mysql 'VALUES 函数' 已弃用 [英] Mysql 'VALUES function' is deprecated

问题描述

这是我打印 sql 查询的 python 代码.

This is my python code which prints the sql query.

def generate_insert_statement(column_names, values_format, table_name, items, insert_template=INSERT_TEMPLATE, ):
    return insert_template.format(
        column_names=",".join(column_names),
        values=",".join(
            map(
                lambda x: generate_raw_values(values_format, x),
                items
            )
        ),
        table_name=table_name,
        updates_on=create_updates_on_columns(column_names)
    )
query = generate_insert_statement(table_name=property['table_name'],
        column_names=property['column_names'],
        values_format=property['values_format'], items=batch)
        print(query) #here
        execute_commit(query)

打印 Mysql 查询时，我的 Django 项目在终端中显示以下错误:

When printing the Mysql query my Django project shows following error in the terminal:

'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead

Mysql doumentation 并没有说太多.这是什么意思，我该如何纠正.

Mysql doumentation does not say much about it.What does this mean and how to can i rectify it.

INSERT_TEMPLATE = "INSERT INTO {table_name} ({column_names}) VALUES {values} ON DUPLICATE KEY UPDATE {updates_on};"

推荐答案

基本上，mysql 正在寻求消除长期存在的对 values 函数的非标准使用，以便为 SQL 标准允许使用 VALUES 的未来工作扫清道路非常不同的关键字.

Basically, mysql is looking toward removing a longstanding non-standard use of the values function to clear the way for some future work where the SQL standard allows using a VALUES keyword for something very different.

您需要为 VALUES 子句添加一个别名，然后在 ON DUPLICATE KEY UPDATE 子句中使用该别名而不是非标准的 VALUES 函数，例如改变

You need to add an alias to the VALUES clause and then use that alias instead of the non-standard VALUES function in the ON DUPLICATE KEY UPDATE clause, e.g. change

INSERT INTO foo (bar, baz) VALUES (1,2)
ON DUPLICATE KEY UPDATE baz=VALUES(baz)

到

INSERT INTO foo (bar, baz) VALUES (1,2) AS new_foo
ON DUPLICATE KEY UPDATE baz=new_foo.baz

来自 https://dev.mysql.com/worklog/task/?id=13325:

按照SQL标准，VALUES是一个返回表的表值构造函数.在 MySQL 中，这适用于简单的 INSERT 和 REPLACE 语句，但 MySQL 还使用 VALUES 来引用 INSERT ... ON DUPLICATE KEY UPDATE 语句中的值.例如:

According to the SQL standard, VALUES is a table value constructor that returns a table. In MySQL this is true for simple INSERT and REPLACE statements, but MySQL also uses VALUES to refer to values in INSERT ... ON DUPLICATE KEY UPDATE statements. E.g.:

在重复键上插入 t(a,b) 值 (1, 2)更新 a = VALUES (b) + 1;

INSERT INTO t(a,b) VALUES (1, 2) ON DUPLICATE KEY UPDATE a = VALUES (b) + 1;

VALUES (b) 指的是 INSERT 表值构造函数中 b 的值，在本例中为 2.

VALUES (b) refers to the value for b in the table value constructor for the INSERT, in this case 2.

为了使简单算术表达式中的值可用，它是 simple_expr 解析器规则的一部分.不幸的是，这也意味着 VALUES 可以在许多其他语句中以这种方式使用，例如:

To make the value available in simple arithmetic expressions, it is part of the parser rule for simple_expr. Unfortunately, this also means that VALUES can be used in this way in a lot of other statements, e.g.:

SELECT a FROM t WHERE a=VALUES(a);

SELECT a FROM t WHERE a=VALUES(a);

在所有此类语句中，VALUES 返回 NULL，因此上述查询不会产生预期效果.VALUES 作为函数而不是表值构造函数的唯一有意义的用法是在 INSERT ... ON DUPLICATE KEY UPDATE 中.此外， INSERT ... ON DUPLICATE KEY UPDATE 中的非标准使用不会扩展到子查询.例如:

In all such statements, VALUES returns NULL, so the above query would not have the intended effect. The only meaningful usage of VALUES as a function, rather than a table value constructor, is in INSERT ... ON DUPLICATE KEY UPDATE. Also, the non-standard use in INSERT ... ON DUPLICATE KEY UPDATE does not extend to subqueries. E.g.:

在重复键上插入 t1 值 (1,2)更新 a=(SELECT a FROM t2 WHERE b=VALUES(b));

INSERT INTO t1 VALUES(1,2) ON DUPLICATE KEY UPDATE a=(SELECT a FROM t2 WHERE b=VALUES(b));

这不符合用户的期望.VALUES(b) 将返回 NULL，即使它在 INSERT .. ON DUPLICATE KEY UPDATE 语句中.

This does not do what the user expects. VALUES(b) will return NULL, even if it is in an INSERT .. ON DUPLICATE KEY UPDATE statement.

非标准语法也使得实现功能 F641行和表构造函数"中指定的 VALUES 的标准行为变得更加困难(不可能?).

The non-standard syntax also makes it harder (impossible?) to implement standard behavior of VALUES as specified in feature F641 "Row and table constructors".

这篇关于Mysql 'VALUES 函数' 已弃用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！