本文介绍了MariaDB 错误的顺序，但在 MySQL 中是正确的的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我的本地服务器上有 MySQL:5.6.17，生产服务器上有 5.5.45-MariaDB-log.给出
解决方案
>选择 NULLIF('2015-11-19 15:08:22', 0);+---------------------------------+|NULLIF('2015-11-19 15:08:22', 0) |+---------------------------------+|2015-11-19 15:08:22 |+---------------------------------+1 行，1 个警告(0.00 秒)>显示警告；+---------+------+---------------------------------------------------------------------+|水平 |代码 |留言 |+---------+------+---------------------------------------------------------------------+|警告 |第1292章截断不正确的 DOUBLE 值:'2015-11-19 15:08:22' |+---------+------+---------------------------------------------------------------------+一组中的 1 行(0.00 秒)>选择 NULLIF('2015-11-19 15:08:22', '0000-00-00 00:00:00');+-------------------------------------------------------+|NULLIF('2015-11-19 15:08:22', '0000-00-00 00:00:00') |+-------------------------------------------------------+|2015-11-19 15:08:22 |+-------------------------------------------------------+一组中的 1 行(0.00 秒)试试:
SELECTe.id,e.dt_competition_last_manual_check,MAX(ec.dt_created) 作为 m，# 如果参数中存在，GREATEST 总是返回 NULLNULLIF(最棒的(COALESCE(MAX(ec.dt_created), '0000-00-00 00:00:00'),合并(e.dt_competition_last_manual_check，'0000-00-00 00:00:00')), '0000-00-00 00:00:00') AS most_recent_dt_created_or_checked从`庄园`作为`e`LEFT JOIN `estates` AS `ec` ON e.id = ec.estates_id_duplicateWHERE e.server = 'esk'GROUP BY `e`.`id`ORDER BY most_recent_dt_created_or_checked DESC；I have MySQL: 5.6.17 on my local server and 5.5.45-MariaDB-log on production server. Giving this fiddle, the resultset is correctly ordered on local server (mysql 5.5 and 5.6 as well), but not on production on mariadb - see image below.. any idea why? is this a mariadb bug?
解决方案
> SELECT NULLIF('2015-11-19 15:08:22', 0);
+----------------------------------+
| NULLIF('2015-11-19 15:08:22', 0) |
+----------------------------------+
| 2015-11-19 15:08:22 |
+----------------------------------+
1 row in set, 1 warning (0.00 sec)
> SHOW WARNINGS;
+---------+------+---------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '2015-11-19 15:08:22' |
+---------+------+---------------------------------------------------------+
1 row in set (0.00 sec)
> SELECT NULLIF('2015-11-19 15:08:22', '0000-00-00 00:00:00');
+------------------------------------------------------+
| NULLIF('2015-11-19 15:08:22', '0000-00-00 00:00:00') |
+------------------------------------------------------+
| 2015-11-19 15:08:22 |
+------------------------------------------------------+
1 row in set (0.00 sec)
Try:
SELECT
e.id,
e.dt_competition_last_manual_check,
MAX(ec.dt_created) as m,
# GREATEST always return NULL if present among arguments
NULLIF(
GREATEST(
COALESCE(MAX(ec.dt_created), '0000-00-00 00:00:00'),
COALESCE(e.dt_competition_last_manual_check, '0000-00-00 00:00:00')
)
, '0000-00-00 00:00:00') AS most_recent_dt_created_or_checked
FROM `estates` AS `e`
LEFT JOIN `estates` AS `ec` ON e.id = ec.estates_id_duplicate
WHERE e.server = 'esk'
GROUP BY `e`.`id`
ORDER BY most_recent_dt_created_or_checked DESC;
这篇关于MariaDB 错误的顺序，但在 MySQL 中是正确的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！