我们有一个名为tblINUser的table，它有许多记录，占用了大量空间。为了减少使用的空间量，我们创建了一个名为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语句。有没有一种方法可以通过一个命令实现同样的效果？
可以合理地预期第一个插入的记录将是
first
我不这么认为。您使用了ORDER BY SortByFilter = 'first'，它为的所有值返回0，但的"first"除外，后面为1表示"first"。值1在值0之后排序，因此条目"first"最终为last。其他值最终或多或少是随机排序的。
演示：
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 |
+-----------+---------------------------+
若要按特定顺序获取行，必须使用ORDER BY。如果ORDER BY的对象是一个字符串并且您想要按字母顺序排列，或者它是数字并且您想要以数字顺序排列，那么这很简单。反面同上，使用DESC。
对于一些异常排序，这里有一个技巧：
ORDER BY FIND_IN_SET(my_column, "first,second,third,fourth")
另一个：
ORDER BY my_column != 'first', my_column
这将首先列出"first"，然后按字母顺序列出其余部分。(我假设my_column是VARCHAR。(
ORDER BY my_column = 'last', my_column
请注意，布尔表达式的计算结果为0(表示false(或1(表示true(；然后，我将取决于0和1的排序顺序。