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)
    
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料