Support MySQL 8.0.19 feature - aliases in the values and the set clause of insert into ... on duplicate key update
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Feature Request
I want TiDB supports one of the MySQL 8.0.19 new feature, aliases in the values and the set clause of insert into ... on duplicate key update. Not only adding as a new feature MySQL 8.0.20 deprecates the "old" syntax.
- MySQL 8.0.19 supports aliases in the VALUES and SET clauses of INSERT INTO ... ON DUPLICATE KEY UPDATE statement https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-19.html
> MySQL now supports aliases in the VALUES and SET clauses of INSERT INTO ... ON DUPLICATE KEY UPDATE statement
> for the row to be inserted and its columns. Consider a statement such as this one:
https://dev.mysql.com/worklog/task/?id=6312
https://github.com/mysql/mysql-server/commit/c39355e9e6985a8aac4df58d8a56ce91f166d27d
- MySQL 8.0.20 deprecates the old `VALUES()` syntax in INSERT ... ON DUPLICATE KEY UPDATE statements
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-20.html
> The use of VALUES() to access new row values in INSERT ... ON DUPLICATE KEY UPDATE statements
> is now deprecated, and is subject to removal in a future MySQL release.
> Instead, you should use aliases for the new row and its columns as implemented in MySQL 8.0.19 and later.
https://dev.mysql.com/worklog/task/?id=13325
https://github.com/mysql/mysql-server/commit/6f3b9df50b6aee5f1f868dc099cc27c660cac130
**Describe the feature you'd like:**
Support aliases in the values and the set clause of insert into ... on duplicate key update.
- MySQL 8.0.20 and newer versions of MySQL supports aliases in the values insert into ... on duplicate key update.
```sql
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.3.0 |
+-----------+
1 row in set (0.00 sec)
mysql> use test;
Database changed
mysql> CREATE TABLE t1
-> (col1 integer primary key,
-> col2 integer
-> );
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> INSERT INTO t1 values(1,1);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> INSERT INTO t1(col1, col2)
-> VALUES (1, 2) AS new(col1, new2)
-> ON DUPLICATE KEY UPDATE col1 = new.col1 + new2;
Query OK, 2 rows affected (0.00 sec)
mysql>
mysql> select * from t1;
+------+------+
| col1 | col2 |
+------+------+
| 3 | 1 |
+------+------+
1 row in set (0.00 sec)
```
- MySQL 8.0.20 and newer versions of MySQL raises the warnings for "old" syntax.
```sql
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.3.0 |
+-----------+
1 row in set (0.00 sec)
mysql> CREATE TABLE t
-> (
-> a integer primary key,
-> b integer
-> );
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql> INSERT INTO t
-> VALUES (9,5), (7,7), (11,-1)
-> ON DUPLICATE KEY UPDATE a = a + VALUES(a) - VALUES(b);
Query OK, 3 rows affected, 2 warnings (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 2
mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | '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 |
| Warning | 1287 | '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 |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
```
**Describe alternatives you've considered:**
Use the "old" syntax.
**Teachability, Documentation, Adoption, Migration Strategy:**
Contributor guide
Assessment
This issue has not been assessed yet.