MariaDB – Generate auto increment column in select query

測試環境為 CentOS 8 x86_64

要如何在 SELECT 時多產生一個自動遞增列的欄位? 可以透過兩種方式.

先建立一個資料庫 (testdb) , 與資料表 (Index1) 資料格式為 Student VARCHAR(200).

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.17-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.00 sec)
 
MariaDB [(none)]> USE testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [testdb]> CREATE TABLE Index1 (Student VARCHAR(200));
Query OK, 0 rows affected (0.003 sec)

建立學生名冊.

MariaDB [testdb]> INSERT INTO Index1 (Student) VALUES ('Ben') , ('Ben1') , ('Ben2') , ('Ben3') , ('Ben4') , ('Ben5');
Query OK, 6 rows affected (0.001 sec)
Records: 6  Duplicates: 0  Warnings: 0

MariaDB [testdb]> SELECT * FROM Index1;
+---------+
| Student |
+---------+
| Ben     |
| Ben1    |
| Ben2    |
| Ben3    |
| Ben4    |
| Ben5    |
+---------+
6 rows in set (0.000 sec)

• @變數
  要在 SELECT 的資料加上序號欄位,可以透過 (@cnt := @cnt + 1) 的方式來做 ( 其中的 := 是將資料儲存到變數).

  MariaDB [testdb]> SET @cnt = 0;
  Query OK, 0 rows affected (0.000 sec)
  
  MariaDB [testdb]> SELECT (@cnt := @cnt + 1) AS Index_Nun , Student FROM Index1;
  +-----------+---------+
  | Index_Nun | Student |
  +-----------+---------+
  |         1 | Ben     |
  |         2 | Ben1    |
  |         3 | Ben2    |
  |         4 | Ben3    |
  |         5 | Ben4    |
  |         6 | Ben5    |
  +-----------+---------+
  6 rows in set (0.000 sec)
  

• ROW_NUMBER()
  透過 ROW_NUMBER() 搭配 OVER (ORDER BY ) 來建立自動增加的序號.

  MariaDB [testdb]> SELECT ROW_NUMBER() OVER(ORDER BY Student) AS Index_Nun , Student FROM Index1;
  +-----------+---------+
  | Index_Nun | Student |
  +-----------+---------+
  |         1 | Ben     |
  |         2 | Ben1    |
  |         3 | Ben2    |
  |         4 | Ben3    |
  |         5 | Ben4    |
  |         6 | Ben5    |
  +-----------+---------+
  6 rows in set (0.001 sec)