如何将"自动递增ID列"添加到SQL查询生成的表中

Question

我有一个从SQL查询本身生成的表。现在我需要在这个表中添加一个自动递增的id列。添加自动增量id列的常用语法是-

ALTER TABLE *Table_Name* ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY

但我没有特定的表名，该表是从查询中生成的。

Answer 1

您可以使用这样的查询：

## Your Query to generate the Tablename
SELECT "myTable" INTO @mytab;
EXECUTE IMMEDIATE CONCAT("ALTER TABLE ",@mytab," ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST");

样本

MariaDB [bernd]> desc myTable;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| insertat | date    | YES  |     | NULL    |       |
| myval    | int(11) | YES  |     | NULL    |       |
+----------+---------+------+-----+---------+-------+
2 rows in set (0.02 sec)
MariaDB [bernd]> select * from myTable;
+------------+-------+
| insertat   | myval |
+------------+-------+
| 2021-01-01 |    44 |
| 2021-01-02 |    99 |
| 2021-01-02 |   134 |
| 2021-01-03 |    45 |
| 2021-01-04 |     2 |
| 2021-01-04 |    17 |
+------------+-------+
6 rows in set (0.06 sec)
MariaDB [bernd]> ## Your Query to generate the Tablename
MariaDB [bernd]> SELECT "myTable" INTO @mytab;
Query OK, 1 row affected (0.01 sec)
MariaDB [bernd]> 
MariaDB [bernd]> EXECUTE IMMEDIATE CONCAT("ALTER TABLE ",@mytab," ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST");
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0
MariaDB [bernd]> desc myTable;
+----------+---------+------+-----+---------+----------------+
| Field    | Type    | Null | Key | Default | Extra          |
+----------+---------+------+-----+---------+----------------+
| id       | int(11) | NO   | PRI | NULL    | auto_increment |
| insertat | date    | YES  |     | NULL    |                |
| myval    | int(11) | YES  |     | NULL    |                |
+----------+---------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
MariaDB [bernd]> select * from myTable;
+----+------------+-------+
| id | insertat   | myval |
+----+------------+-------+
|  1 | 2021-01-01 |    44 |
|  2 | 2021-01-02 |    99 |
|  3 | 2021-01-02 |   134 |
|  4 | 2021-01-03 |    45 |
|  5 | 2021-01-04 |     2 |
|  6 | 2021-01-04 |    17 |
+----+------------+-------+
6 rows in set (0.00 sec)
MariaDB [bernd]>