MariaDb 不支持 ANY_VALUE() 函数

2022-01-15 00:00:00 sql mariadb php mysql laravel

我有一个连接到 mysql db 的 laravel 项目,当我更改服务器时,我的代码失败了,因为我的新服务器有一个 Mariadb,当我检查我的日志时,我意识到, MariaDb 有一些不受支持的函数是 ANY_VALUE(),

I have a laravel project which is connected to mysql db, when I change my server, my codes got failed because my new server has a Mariadb, when I checked my logs, I have realised that, there is some unsupported function from MariaDb which is ANY_VALUE(),

如何根据 MariaDb 编辑我的 sql?

how can I edit my sql according to MariaDb ?

select(DB::raw('SUM(price) as price, SUM(price_now) as price_now, 
   ANY_VALUE(price_available) as price_available'),'adult_count')

错误日志

推荐答案

今天,你已经解决了问题.但是明天,当你运行同样的查询时,你会得到一个不同的错误.

For today, you have solved the problem. But tomorrow, when you run the same query, you will get a different error.

在旧版本的 MySQL 或 MariaDB 中,您将获得任何值";对于 price_available 不是 GROUPing BY 它.这实际上介于不良做法"和不良做法"之间.和违反标准".相对最近,MariaDB,然后是后来的 MySQL,切换到only full group by".那时,ANY_VALUE() 为 MySQL 应运而生,但显然 MariaDB 丢了球.

In older versions of MySQL or MariaDB, you would get "any value" for price_available when not GROUPing BY it. That was effectively somewhere between "bad practice" and a "standards violation". Relatively recently, MariaDB, then later MySQL, switched to "only full group by". At that time, ANY_VALUE() came into existence for MySQL, but apparently MariaDB dropped the ball.

对于旧版本和新版本都应该安全的旧解决方法是使用 MIN(price_available) 或其他一些聚合函数.(如果列可能有 NULL,则各种聚合可能会也可能不会按照您喜欢的方式处理 NULL.)

The old workaround, which should be safe for both old and new versions is to use MIN(price_available) or some other aggregate function. (If the column might have NULL, the various aggregates might or might not handle NULL the way you prefer.)

另请参阅 ONLY_FULL_GROUP_BY 设置.

相关文章