如何维护insert-select脚本的排序？

Question

我们有一个名为table的tblINUser，它有许多记录，占用了大量的空间。为了减少使用的空间，我们创建了一个名为tblINUserSortByFilter的表，该表包含该字段的所有可能的文本值，我们在tblINUser中创建了一个外键，该外键在数字上引用该值。我们有几个数据库，因为这个数据库是切分的，所以最好以类似的方式对数据库中的值进行排序。这是第一次尝试：

CREATE TABLE MC.tblINUserSortByFilterType(
    pkINUserSortByFilterTypeID SMALLINT(6) PRIMARY KEY AUTO_INCREMENT,
    SortByFilter varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'first',
    INDEX(SortByFilter)
);

INSERT INTO MC.tblINUserSortByFilterType(SortByFilter)
SELECT DISTINCT SortByFilter
FROM MC.tblINUser
ORDER BY SortByFilter = 'first';

ALTER TABLE MC.tblINUser
ADD COLUMN fkINUserSortByFilterTypeID SMALLINT(6) DEFAULT 1,
ADD INDEX (fkINUserSortByFilterTypeID);

UPDATE MC.tblINUser INUser
JOIN MC.tblINUserSortByFilterType INUserSortByFilterType
ON INUser.SortByFilter = INUserSortByFilterType.SortByFilter
SET INUser.fkINUserSortByFilterTypeID = INUserSortByFilterType.pkINUserSortByFilterTypeID;

ALTER TABLE MC.tblINUser
DROP COLUMN SortByFilter;

您可能会正确地认为，排序有唯一的条件，即ORDER BY SortByFilter = 'first'和ORDER BY SortByFilter = 'first', SortByFilter的一个子句将是一个明显的改进。这将是一个正确的批评，然而，尽管我们可能从第二个记录开始就有一种混乱的行为，我们有理由期望第一个插入的记录将是first，但不幸的是，情况并非如此。运行select * from MC.tblINUserSortByFilterType;产量

+----------------------------+----------------------------+                                       
| pkINUserSortByFilterTypeID | SortByFilter               |
+----------------------------+----------------------------+                           
|                          5 | first                      |                                                                                                                                                 
|                          4 | first-ASC                  |                                                                                                                                                 
|                          3 | last                       |                             
|                          1 | none                       |                  
|                          2 | StatTeacher.IsActive DESC  |                                                                                                                                                
+----------------------------+----------------------------+

正如我们所看到的，由于first的id为5，甚至没有满足这个期望。

INSERT INTO MC.tblINUserSortByFilterType(SortByFilter)
SELECT DISTINCT SortByFilter
FROM MC.tblINUser
WHERE SortByFilter = 'first';

INSERT INTO MC.tblINUserSortByFilterType(SortByFilter)
SELECT DISTINCT SortByFilter
FROM MC.tblINUser
WHERE SortByFilter <> 'first';

然后，在相同的选择下，我们得到了这样的结果：

+----------------------------+----------------------------+
| pkINUserSortByFilterTypeID | SortByFilter               |
+----------------------------+----------------------------+
|                          1 | first                      |
|                          3 | first-ASC                  |
|                          4 | last                       |
|                          2 | none                       |
|                          5 | StatTeacher.IsActive DESC  |
+----------------------------+----------------------------+
5 rows in set (0.00 sec)

正如我们所看到的，first正确地接收到了1的值。然而，如果我们在不同的数据库副本上运行相同的插入，那么后续行的顺序可能是不可靠的。那么，我们如何确保按以下查询生成的准确顺序插入记录？

SELECT DISTINCT SortByFilter
FROM MC.tblINUser
WHERE SortByFilter = 'first', SortByFilter;

我知道我们可以通过

对insert

• looping使用游标的记录received

• inserting

• individually

但是，这将有与上面查询产生的记录数一样多的insert语句。是否有一种方法可以通过一个命令来实现相同的目标？

Answer 1

可以合理地预期第一个插入的记录将是first

我不这样认为。您使用的是ORDER BY SortByFilter = 'first'，除了'first‘之外，所有值都返回0，后面是'first'，后面是1。值1在值0之后排序，所以条目'first‘最后是最后一个。其他值的排序或多或少是随机的。

演示：

mysql> create table mytable (SortByFilter varchar(64));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into mytable values ('first'), ('first-ASC'), 
  ('last'), ('none'), ('StatTeacher.IsActive DESC');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select SortByFilter='first', SortByFilter from mytable 
  order by SortByFilter = 'first';
+----------------------+---------------------------+
| SortByFilter='first' | SortByFilter              |
+----------------------+---------------------------+
|                    0 | first-ASC                 |
|                    0 | last                      |
|                    0 | none                      |
|                    0 | StatTeacher.IsActive DESC |
|                    1 | first                     |
+----------------------+---------------------------+

我建议不要依赖自动分类。具体说明每个值的排序顺序。有一种方法可以做到：

mysql> select field(SortByFilter, 'first', 'first-ASC',
  'none', 'StatTeacher.IsActive DESC', 'last') AS SortOrder, 
  SortByFilter 
  from mytable order by SortOrder;
+-----------+---------------------------+
| SortOrder | SortByFilter              |
+-----------+---------------------------+
|         1 | first                     |
|         2 | first-ASC                 |
|         3 | none                      |
|         4 | StatTeacher.IsActive DESC |
|         5 | last                      |
+-----------+---------------------------+

Answer 2

要按特定顺序获取行，必须使用ORDER BY。如果ORDER BY的对象是一个字符串，并且您想要按字母顺序排列，或者它是数字的，并且您希望它按数字顺序排列，那么这样做很简单。使用DESC进行反向操作也是一样的。

对于一些不正常的排序，这里有一个技巧：

ORDER BY FIND_IN_SET(my_column, "first,second,third,fourth")

另一个：

ORDER BY my_column != 'first', my_column

这将首先列出“第一”，然后按字母顺序进行其余的排序。(我假设my_column是VARCHAR。)

ORDER BY my_column = 'last', my_column

注意，布尔表达式的计算值为0(表示false)或1 (表示true)；然后，我将根据0和1的排序顺序而定。