SQLite 的 AUTOINCREMENT 是一个关键字，用于表中的字段值自动递增。我们可以在创建表时在特定的列名称上使用 AUTOINCREMENT 关键字实现该字段值的自动增加。
关键字 AUTOINCREMENT 只能用于整型（INTEGER）字段。
语法
AUTOINCREMENT 关键字的基本用法如下：
|
1 2 3 4 5 6 7 |
<span class="pln">CREATE TABLE table_name</span><span class="pun">(</span><span class="pln"> column1 INTEGER AUTOINCREMENT</span><span class="pun">,</span><span class="pln"> column2 datatype</span><span class="pun">,</span><span class="pln"> column3 datatype</span><span class="pun">,</span> <span class="pun">.....</span><span class="pln"> columnN datatype</span><span class="pun">,</span> <span class="pun">);</span> |
实例
假设要创建的 COMPANY 表如下所示：
|
1 2 3 4 5 6 7 |
<span class="pln">sqlite</span><span class="pun">></span><span class="pln"> CREATE TABLE COMPANY</span><span class="pun">(</span><span class="pln"> ID INTEGER PRIMARY KEY AUTOINCREMENT</span><span class="pun">,</span><span class="pln"> NAME TEXT NOT NULL</span><span class="pun">,</span><span class="pln"> AGE INT NOT NULL</span><span class="pun">,</span><span class="pln"> ADDRESS CHAR</span><span class="pun">(</span><span class="lit">50</span><span class="pun">),</span><span class="pln"> SALARY REAL </span><span class="pun">);</span> |
现在，向 COMPANY 表插入以下记录：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
<span class="pln">INSERT INTO COMPANY </span><span class="pun">(</span><span class="pln">NAME</span><span class="pun">,</span><span class="pln">AGE</span><span class="pun">,</span><span class="pln">ADDRESS</span><span class="pun">,</span><span class="pln">SALARY</span><span class="pun">)</span><span class="pln"> VALUES </span><span class="pun">(</span> <span class="str">'Paul'</span><span class="pun">,</span> <span class="lit">32</span><span class="pun">,</span> <span class="str">'California'</span><span class="pun">,</span> <span class="lit">20000.00</span> <span class="pun">);</span><span class="pln"> INSERT INTO COMPANY </span><span class="pun">(</span><span class="pln">NAME</span><span class="pun">,</span><span class="pln">AGE</span><span class="pun">,</span><span class="pln">ADDRESS</span><span class="pun">,</span><span class="pln">SALARY</span><span class="pun">)</span><span class="pln"> VALUES </span><span class="pun">(</span><span class="str">'Allen'</span><span class="pun">,</span> <span class="lit">25</span><span class="pun">,</span> <span class="str">'Texas'</span><span class="pun">,</span> <span class="lit">15000.00</span> <span class="pun">);</span><span class="pln"> INSERT INTO COMPANY </span><span class="pun">(</span><span class="pln">NAME</span><span class="pun">,</span><span class="pln">AGE</span><span class="pun">,</span><span class="pln">ADDRESS</span><span class="pun">,</span><span class="pln">SALARY</span><span class="pun">)</span><span class="pln"> VALUES </span><span class="pun">(</span><span class="str">'Teddy'</span><span class="pun">,</span> <span class="lit">23</span><span class="pun">,</span> <span class="str">'Norway'</span><span class="pun">,</span> <span class="lit">20000.00</span> <span class="pun">);</span><span class="pln"> INSERT INTO COMPANY </span><span class="pun">(</span><span class="pln">NAME</span><span class="pun">,</span><span class="pln">AGE</span><span class="pun">,</span><span class="pln">ADDRESS</span><span class="pun">,</span><span class="pln">SALARY</span><span class="pun">)</span><span class="pln"> VALUES </span><span class="pun">(</span> <span class="str">'Mark'</span><span class="pun">,</span> <span class="lit">25</span><span class="pun">,</span> <span class="str">'Rich-Mond '</span><span class="pun">,</span> <span class="lit">65000.00</span> <span class="pun">);</span><span class="pln"> INSERT INTO COMPANY </span><span class="pun">(</span><span class="pln">NAME</span><span class="pun">,</span><span class="pln">AGE</span><span class="pun">,</span><span class="pln">ADDRESS</span><span class="pun">,</span><span class="pln">SALARY</span><span class="pun">)</span><span class="pln"> VALUES </span><span class="pun">(</span> <span class="str">'David'</span><span class="pun">,</span> <span class="lit">27</span><span class="pun">,</span> <span class="str">'Texas'</span><span class="pun">,</span> <span class="lit">85000.00</span> <span class="pun">);</span><span class="pln"> INSERT INTO COMPANY </span><span class="pun">(</span><span class="pln">NAME</span><span class="pun">,</span><span class="pln">AGE</span><span class="pun">,</span><span class="pln">ADDRESS</span><span class="pun">,</span><span class="pln">SALARY</span><span class="pun">)</span><span class="pln"> VALUES </span><span class="pun">(</span> <span class="str">'Kim'</span><span class="pun">,</span> <span class="lit">22</span><span class="pun">,</span> <span class="str">'South-Hall'</span><span class="pun">,</span> <span class="lit">45000.00</span> <span class="pun">);</span><span class="pln"> INSERT INTO COMPANY </span><span class="pun">(</span><span class="pln">NAME</span><span class="pun">,</span><span class="pln">AGE</span><span class="pun">,</span><span class="pln">ADDRESS</span><span class="pun">,</span><span class="pln">SALARY</span><span class="pun">)</span><span class="pln"> VALUES </span><span class="pun">(</span> <span class="str">'James'</span><span class="pun">,</span> <span class="lit">24</span><span class="pun">,</span> <span class="str">'Houston'</span><span class="pun">,</span> <span class="lit">10000.00</span> <span class="pun">);</span> |
这将向 COMPANY 表插入 7 个元组，此时 COMPANY 表的记录如下：
|
1 2 3 4 5 6 7 8 9 |
<span class="pln">ID NAME AGE ADDRESS SALARY </span><span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="lit">1</span> <span class="typ">Paul</span> <span class="lit">32</span> <span class="typ">California</span> <span class="lit">20000.0</span> <span class="lit">2</span> <span class="typ">Allen</span> <span class="lit">25</span> <span class="typ">Texas</span> <span class="lit">15000.0</span> <span class="lit">3</span> <span class="typ">Teddy</span> <span class="lit">23</span> <span class="typ">Norway</span> <span class="lit">20000.0</span> <span class="lit">4</span> <span class="typ">Mark</span> <span class="lit">25</span> <span class="typ">Rich</span><span class="pun">-</span><span class="typ">Mond</span> <span class="lit">65000.0</span> <span class="lit">5</span> <span class="typ">David</span> <span class="lit">27</span> <span class="typ">Texas</span> <span class="lit">85000.0</span> <span class="lit">6</span> <span class="typ">Kim</span> <span class="lit">22</span> <span class="typ">South</span><span class="pun">-</span><span class="typ">Hall</span> <span class="lit">45000.0</span> <span class="lit">7</span> <span class="typ">James</span> <span class="lit">24</span> <span class="typ">Houston</span> <span class="lit">10000.0</span> |