在MySQL中插入序号？
您可以使用会话变量在MySQL中插入序号。语法如下-
SELECT @anyVariableName − = anyIntegerValue; UPDATE yourTableName SET yourColumnName = @anyVariableName − = @anyVariableName+IncrementStep;
为了理解上述语法，让我们创建一个表。创建表的查询如下-
mysql> create table SequentialNumberDemo -> ( -> SequentialNumber int not null -> );
使用insert命令在表中插入记录。查询如下-
mysql> insert into SequentialNumberDemo values(100); mysql> insert into SequentialNumberDemo values(10); mysql> insert into SequentialNumberDemo values(9); mysql> insert into SequentialNumberDemo values(60); mysql> insert into SequentialNumberDemo values(50); mysql> insert into SequentialNumberDemo values(40);
使用select语句显示表中的所有记录。查询如下-
mysql> select *from SequentialNumberDemo;
以下是输出-
+------------------+ | SequentialNumber | +------------------+ | 100 | | 10 | | 9 | | 60 | | 50 | | 40 | +------------------+ 6 rows in set (0.00 sec)
查看上面的输出，该数字不是按顺序排列的。这是获取从1开始的序列号的查询。首先，设置序列-
mysql> select @sequence: = 0;
输出-
+---------------+ | @sequence:= 0 | +---------------+ | 0 | +---------------+ 1 row in set (0.03 sec)
现在，查询以更新并从1开始序列-
mysql> update SequentialNumberDemo set SequentialNumber = @sequence − = @sequence+1; Rows matched − 6 Changed − 6 Warnings − 0
再次检查表记录。查询如下-
mysql> select *from SequentialNumberDemo;
以下是输出-
+------------------+ | SequentialNumber | +------------------+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | +------------------+ 6 rows in set (0.00 sec)