文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >为什么“选择”在MariaDB中有效，而在MySQL中不起作用

问为什么“选择”在MariaDB中有效，而在MySQL中不起作用
EN

Database Administration用户
提问于 2015-09-05 07:23:01
回答 1查看 2.4K关注 0票数 4
代码语言：javascript
复制
set @row_number = 0;
SELECT 
    *
FROM
    (SELECT 
        (@row_number:=@row_number + 1) AS num,
        id,
        tbl_user_id,
        title,
        description,
        length lengths,
        create_date,
        file_size,
        thumbnails,
        videos.itsOK,
        viewed
    FROM
        tbl_videos videos
    WHERE
        videos.tbl_user_id = 23
            AND videos.tbl_category_id = 265
        ORDER BY videos.create_date DESC
) AS paginateTbl
WHERE
    paginateTbl.num > 0
        && paginateTbl.num <= 9

mysql结果：

mariadb的结果：

内部查询工作在这两个方面，但主要查询工作仅在mariadb！mysql有什么问题不起作用？

使用的版本是mysql：5.5.44-0ubuntu0.14.04.1和mariadb 10.0.13-MariaDB-log。

CREATE TABLE语句是相同的(除了AUTO_INCREMENT，行数)：

MySQL结果：

代码语言：javascript
复制
SHOW CREATE TABLE tbl_videos;

CREATE TABLE `tbl_videos` (
    `id` INT (20) NOT NULL AUTO_INCREMENT
    ,`title` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`description` TEXT COLLATE utf8_persian_ci NOT NULL
    ,`tags` TEXT COLLATE utf8_persian_ci NOT NULL
    ,`video_quality` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`dl_link1` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`dl_link2` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`dl_link3` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`viewed` INT (11) NOT NULL
    ,`viewed_duration` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`viewed_traffic` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`embed_code` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`sharing_code` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`replace_times` INT (11) NOT NULL
    ,`actual_link` TEXT COLLATE utf8_persian_ci NOT NULL
    ,`tbl_user_id` INT (11) NOT NULL
    ,`tbl_category_id` INT (11) NOT NULL
    ,`tbl_player_id` INT (11) NOT NULL
    ,`itsOK` TINYINT (2) NOT NULL
    ,`length` INT (20) NOT NULL
    ,`create_date` INT (11) NOT NULL
    ,`modified_date` INT (11) NOT NULL
    ,`thumbnails` TEXT COLLATE utf8_persian_ci
    ,`serverId` VARCHAR(32) COLLATE utf8_persian_ci NOT NULL
    ,`sizes` VARCHAR(100) COLLATE utf8_persian_ci DEFAULT NULL
    ,`our_server_link` VARCHAR(255) COLLATE utf8_persian_ci DEFAULT NULL
    ,`like` INT (11) NOT NULL DEFAULT '0'
    ,`file_size` FLOAT DEFAULT NULL
    ,`islogo` TEXT COLLATE utf8_persian_ci
    ,`uuid` VARCHAR(64) COLLATE utf8_persian_ci DEFAULT NULL
    ,`output_type` VARCHAR(255) COLLATE utf8_persian_ci DEFAULT NULL
    ,`video_file` VARCHAR(255) COLLATE utf8_persian_ci DEFAULT NULL
    ,`video_setting` TEXT COLLATE utf8_persian_ci NOT NULL
    ,`soft_hard` VARCHAR(255) COLLATE utf8_persian_ci DEFAULT NULL
    ,`soft_hard_logo` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`vastTag` TEXT COLLATE utf8_persian_ci
    ,`extra_cat_id` INT (11) NOT NULL DEFAULT '0'
    ,`all_terafic` BIGINT (20) NOT NULL DEFAULT '0'
    ,PRIMARY KEY (`id`)
    ,KEY `tbl_user_id`(`tbl_user_id`)
    ,KEY `tbl_category_id`(`tbl_category_id`)
    ,CONSTRAINT `tbl_videos_ibfk_1` FOREIGN KEY (`tbl_user_id`) REFERENCES `tbl_users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ,CONSTRAINT `tbl_videos_ibfk_2` FOREIGN KEY (`tbl_category_id`) REFERENCES `tbl_categories`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE = InnoDB AUTO_INCREMENT = 4622 DEFAULT CHARSET = utf8 COLLATE = utf8_persian_ci

MariaDB结果：

代码语言：javascript
复制
SHOW CREATE TABLE tbl_videos;

CREATE TABLE `tbl_videos` (
    `id` INT (20) NOT NULL AUTO_INCREMENT
    ,`title` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`description` TEXT COLLATE utf8_persian_ci NOT NULL
    ,`tags` TEXT COLLATE utf8_persian_ci NOT NULL
    ,`video_quality` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`dl_link1` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`dl_link2` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`dl_link3` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`viewed` INT (11) NOT NULL
    ,`viewed_duration` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`viewed_traffic` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`embed_code` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`sharing_code` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`replace_times` INT (11) NOT NULL
    ,`actual_link` TEXT COLLATE utf8_persian_ci NOT NULL
    ,`tbl_user_id` INT (11) NOT NULL
    ,`tbl_category_id` INT (11) NOT NULL
    ,`tbl_player_id` INT (11) NOT NULL
    ,`itsOK` TINYINT (2) NOT NULL
    ,`length` INT (20) NOT NULL
    ,`create_date` INT (11) NOT NULL
    ,`modified_date` INT (11) NOT NULL
    ,`thumbnails` TEXT COLLATE utf8_persian_ci
    ,`serverId` VARCHAR(32) COLLATE utf8_persian_ci NOT NULL
    ,`sizes` VARCHAR(100) COLLATE utf8_persian_ci DEFAULT NULL
    ,`our_server_link` VARCHAR(255) COLLATE utf8_persian_ci DEFAULT NULL
    ,`like` INT (11) NOT NULL DEFAULT '0'
    ,`file_size` FLOAT DEFAULT NULL
    ,`islogo` TEXT COLLATE utf8_persian_ci
    ,`uuid` VARCHAR(64) COLLATE utf8_persian_ci DEFAULT NULL
    ,`output_type` VARCHAR(255) COLLATE utf8_persian_ci DEFAULT NULL
    ,`video_file` VARCHAR(255) COLLATE utf8_persian_ci DEFAULT NULL
    ,`video_setting` TEXT COLLATE utf8_persian_ci NOT NULL
    ,`soft_hard` VARCHAR(255) COLLATE utf8_persian_ci DEFAULT NULL
    ,`soft_hard_logo` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`vastTag` TEXT COLLATE utf8_persian_ci
    ,`extra_cat_id` INT (11) NOT NULL DEFAULT '0'
    ,`all_terafic` BIGINT (20) NOT NULL DEFAULT '0'
    ,PRIMARY KEY (`id`)
    ,KEY `tbl_user_id`(`tbl_user_id`)
    ,KEY `tbl_category_id`(`tbl_category_id`)
    ,CONSTRAINT `tbl_videos_ibfk_1` FOREIGN KEY (`tbl_user_id`) REFERENCES `tbl_users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ,CONSTRAINT `tbl_videos_ibfk_2` FOREIGN KEY (`tbl_category_id`) REFERENCES `tbl_categories`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE = InnoDB AUTO_INCREMENT = 9387 DEFAULT CHARSET = utf8 COLLATE = utf8_persian_ci

mysql结果：

代码语言：javascript
复制
EXPLAIN SELECT * from FROM ...
id  select_type table       type    possible_keys   key key_len ref rows    Extra
1   PRIMARY     <derived2>  ALL NULL    NULL    NULL    NULL    14  Using where
2   DERIVED     videos      index_merge tbl_user_id,tbl_category_id tbl_category_id,tbl_user_id 4,4 NULL    1   Using intersect(tbl_category_id,tbl_user_id); Using where; Using filesort

mariadb的结果：

代码语言：javascript
复制
EXPLAIN SELECT * from tbl_videos
id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    2   Using where
2   DERIVED videos  index_merge tbl_user_id,tbl_category_id tbl_category_id,tbl_user_id 4,4 NULL    1   Using intersect(tbl_category_id,tbl_user_id); Using where; Using filesort
mysql
mariadb
EN

回答 1

Database Administration用户

回答已采纳

发布于 2015-09-07 05:42:56

是MySQL 5.5bug，是向MySQL报告。因此，我安装了MySQL5.6，主查询运行良好。与结果中的@@version相同的查询。

代码语言：javascript
复制
set @row_number = 0;
SELECT 
    *, @@version mysql_version
FROM
    (SELECT 
        (@row_number:=@row_number + 1) AS num,
        id,
        tbl_user_id,
        title,
        description,
        length lengths,
        create_date,
        file_size,
        thumbnails,
        videos.itsOK,
        viewed
    FROM
        tbl_videos videos
    WHERE
        videos.tbl_user_id = 9
            AND videos.tbl_category_id = 113
            AND length > 0
        ORDER BY videos.create_date ASC
) AS paginateTbl
WHERE
    paginateTbl.num > 0
        && paginateTbl.num <= 9

mysql 5.5和MySQL5.6主要查询结果：

现在，我在tbl_videos中检查了一个特殊的id例如: 1103，这两个选择都很好。

代码语言：javascript
复制
SELECT 
    id,
    tbl_user_id,
    title,
    description,
    length lengths,
    create_date,
    file_size,
    thumbnails,
    itsOK,
    viewed,
    @@version mysql_version
FROM
    tbl_videos
WHERE
    id = 1103

mysql 5.5和mysql 5.6结果：

票数 4
EN
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://dba.stackexchange.com/questions/114256

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档