MySQL:按顺序插入选择

Question

我想按特定顺序将数据插入表中。这是因为我需要为每个条目指定一个特定的 ID。我使用的是 select 语句:

select (@i := @i + 1) as id, ...
order by column

我遇到的问题是这似乎不起作用。我从选择查询中得到了我想要的结果。但是，当我尝试将数据插入表中时，order by 语句将被忽略。有没有办法强制插入语句中的顺序正确？

我想要的是这样的:

+----+------+-------------+
| id | name | breadcrumbs |
+----+------+-------------+
|  1 | test | 01          |
|  5 | -d   | 01,05       |
|  4 | c    | 04          |
|  6 | e    | 06          |
|  2 | -a   | 06,02       |
|  3 | --b  | 06,02,03    |
+----+------+-------------+

变成这样:

+----+------+-------------+
| id | name | breadcrumbs |
+----+------+-------------+
|  1 | test | 01          |
|  2 | -d   | 01,05       |
|  3 | c    | 04          |
|  4 | e    | 06          |
|  5 | -a   | 06,02       |
|  6 | --b  | 06,02,03    |
+----+------+-------------+

在单独的临时表中。

Answer 1

我会确保 @i 已初始化，请参阅下面的 select in from 子句

MariaDB [sandbox]> drop table if exists t;
Query OK, 0 rows affected (0.14 sec)

MariaDB [sandbox]>
MariaDB [sandbox]> create table t(id int, name varchar(10), breadcrumbs varchar(100));
Query OK, 0 rows affected (0.18 sec)

MariaDB [sandbox]> insert into t values
    -> (  1 , 'test' , '01'      ),
    -> (  5 , '-d'   , '01,05'   ),
    -> (  4 , 'c'    , '04'      ),
    -> (  6 , 'e'    , '06'      ),
    -> (  2 , '-a'   , '06,02'   ),
    -> (  3 , '--b'  , '06,02,03');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

MariaDB [sandbox]>
MariaDB [sandbox]> drop table if exists t1;
Query OK, 0 rows affected (0.13 sec)

MariaDB [sandbox]> create table t1 as
    -> select
    -> @i:=@i+1 id,
    ->  t.name,t.breadcrumbs
    -> from  (select @i:=0) i,
    -> t
    -> order by breadcrumbs;
Query OK, 6 rows affected (0.22 sec)
Records: 6  Duplicates: 0  Warnings: 0

MariaDB [sandbox]>
MariaDB [sandbox]> select * from t1;
+------+------+-------------+
| id   | name | breadcrumbs |
+------+------+-------------+
|    1 | test | 01          |
|    2 | -d   | 01,05       |
|    3 | c    | 04          |
|    4 | e    | 06          |
|    5 | -a   | 06,02       |
|    6 | --b  | 06,02,03    |
+------+------+-------------+
6 rows in set (0.00 sec)