gpt4 book ai didi

Mysql 的 IN 和 ORDER BY 子句问题

转载 作者:行者123 更新时间:2023-11-29 22:38:30 24 4
gpt4 key购买 nike

我在查询以下 Mysql 表时遇到了一个奇怪的问题:

CREATE TABLE `BulkTransuploads` (
`bulktransuploads_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id of the upload',
`bulktransuploads_time` datetime NOT NULL COMMENT 'The upload time',
`bulktransuploads_val_id` int(10) unsigned NOT NULL COMMENT 'TransactionID value. value 1',
`bulktransuploads_val_orderid` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'OrderID value. value 2',
`bulktransuploads_val_url` varchar(400) NOT NULL COMMENT 'URL value. value 3',
`bulktransuploads_val_tracking` varchar(40) NOT NULL COMMENT 'Tracking value. value 4',
`bulktransuploads_status_v1` varchar(150) NOT NULL COMMENT 'Status for value1. OK or error message',
`bulktransuploads_status_v2` varchar(150) NOT NULL COMMENT 'Status for value2. OK or error message',
`bulktransuploads_status_v3` varchar(150) NOT NULL COMMENT 'Status for value3. OK or error message',
`bulktransuploads_status_v4` varchar(150) NOT NULL COMMENT 'Status for value4. OK or error message',
PRIMARY KEY (`bulktransuploads_id`)
) ENGINE=ARCHIVE AUTO_INCREMENT=242 DEFAULT CHARSET=utf8

我需要提取一些具有 ids 的值,所以我认为最短的方法是使用 IN 子句。

mysql> SELECT * FROM BulkTransuploads WHERE bulktransuploads_id IN (233,231,232)\G;

上述查询返回以下结果:

*************************** 1. row ***************************
bulktransuploads_id: 231
*************************** 2. row ***************************
bulktransuploads_id: 232
*************************** 3. row ***************************
bulktransuploads_id: 233
3 rows in set (0.00 sec)

我删除了其他列值以节省一些空间。总而言之,这个查询几乎符合我的预期,尽管我期望不同的顺序(233,231,232),因为这个顺序是在查询(IN 子句)中写入的。由于我需要将结果按降序排列,因此我修改了查询,如下所示:

mysql> SELECT * FROM BulkTransuploads WHERE bulktransuploads_id IN (233,231,232) ORDER BY bulktransuploads_id DESC\G;

我的新查询返回一个尴尬的结果:

*************************** 1. row ***************************
bulktransuploads_id: 200
*************************** 2. row ***************************
bulktransuploads_id: 200
*************************** 3. row ***************************
bulktransuploads_id: 200
3 rows in set (0.00 sec)

我无法理解为什么它返回 3 次 id 200。查询之间的唯一区别是 ORDER BYbulktransuploads_id DESC ,因此毫无疑问 ORDER BY 子句会弄乱我的结果,但我的问题是为什么?

最佳答案

You need to change the ENGINE that you're using, instead use InnoDB.

将 CREATE TABLE 查询修改为:

CREATE TABLE `BulkTransuploads` (
`bulktransuploads_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id of the upload',
`bulktransuploads_time` datetime NOT NULL COMMENT 'The upload time',
`bulktransuploads_val_id` int(10) unsigned NOT NULL COMMENT 'TransactionID value. value 1',
`bulktransuploads_val_orderid` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'OrderID value. value 2',
`bulktransuploads_val_url` varchar(400) NOT NULL COMMENT 'URL value. value 3',
`bulktransuploads_val_tracking` varchar(40) NOT NULL COMMENT 'Tracking value. value 4',
`bulktransuploads_status_v1` varchar(150) NOT NULL COMMENT 'Status for value1. OK or error message',
`bulktransuploads_status_v2` varchar(150) NOT NULL COMMENT 'Status for value2. OK or error message',
`bulktransuploads_status_v3` varchar(150) NOT NULL COMMENT 'Status for value3. OK or error message',
`bulktransuploads_status_v4` varchar(150) NOT NULL COMMENT 'Status for value4. OK or error message',
PRIMARY KEY (`bulktransuploads_id`)
) ENGINE=InnoDB AUTO_INCREMENT=200 DEFAULT CHARSET=utf8;

关于Mysql 的 IN 和 ORDER BY 子句问题,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/29460045/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com