我有一個從 SQL 查詢本身生成的表。現在我需要在這個表中添加一個自動增量 id 列。添加自動增量 id 列的常用語法是-
ALTER TABLE *Table_Name* ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
但是我沒有特定的表名，該表是從查詢生成的。
uj5u.com熱心網友回復：
你可以用這樣的查詢來做到這一點：
## 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]>
uj5u.com熱心網友回復：
生成表后，您可以使用此語法在查詢中添加行號：
select R.*,
@rownum := @rownum 1 as row_number
from GENERATED_TABLE R
cross join (select @rownum := 0) r
注意：*Table_Name*-->GENERATED_TABLE
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/357302.html