mysql 修改sql_mode 实现字符串管道‘||’连接

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/royjj/article/details/60572357
mysql> show variables like '%sql_mode%';
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.00 sec)


mysql> select 'a'||'a';
+----------+
| 'a'||'a' |
+----------+
|        0 |
+----------+
1 row in set, 2 warnings (0.00 sec)


在oracle 缺省支持 通过 ‘ || ’ 来实现字符串拼接,但在mysql 缺省不支持。需要调整mysql 的sql_mode 模式:pipes_as_concat 来实现oracle 的一些功能


mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,pipes_as_concat';
Query OK, 0 rows affected (0.00 sec)


mysql> select 'a'||'a';
+----------+
| 'a'||'a' |
+----------+
| aa       |
+----------+
1 row in set (0.00 sec)


mysql> 
展开阅读全文

mysql 5.7 sql_mode修改后无效

10-14

mysql 5.7 默认开启了sql_mode的条件限制, 但是我修改该后,仍然无效, 不管是通过控制台里面输入 set sql_mode='', set gloale sql_mode = '', set session sql_mode = '', 还是在my.cfn文件里面的 [mysqld] 选项下加入 sql_mode='', 都无法生效,rn直接贴图吧rn[img=https://img-bbs.csdn.net/upload/201610/14/1476440115_580176.png][/img]rnrn存储过程如下, 存储过程中有对select * 出来的东西进行group by操作, mysql 5.7默认不支持了, 需要group by的字段,必须要select该字段才可以, 查看过mysql的官方手册,他们就是说通上面的几个set命令就可以取消这个限制, 真是无解了,折腾两天了,求大神指教rn[code=sql]rnDELIMITER $$rnUSE `qp_game_loggers`$$rnDROP PROCEDURE IF EXISTS `MB_GR_GetBoxResult`$$rnCREATE PROCEDURE `MB_GR_GetBoxResult`(IN iKindId INT(11), IN iUserId INT(11))rnLABEL_PROC:rnBEGINrnIF iKindId = 0 THENrn SELECT *, SUM(Score) AS TotalScore,MAX(curgame) AS RoundCount FROM gamebox_game_result WHERE boxid INrn (SELECT boxid FROM gamebox_game_result WHERE userid =iUserId rn AND CollectTime >= NOW() - INTERVAL 3600*24*5 SECOND rn ORDER BY CollectTime DESC) GROUP BY userid, boxid ORDER BY CollectTime DESC;rnELSErn SELECT *,SUM(Score) AS TotalScore, MAX(curgame) AS RoundCount FROM gamebox_game_result WHERE boxid INrn (SELECT boxid FROM gamebox_game_result WHERE userid =iUserId rn AND kindid=iKindId AND CollectTime >= NOW() - INTERVAL 3600*24*5 SECOND rn ORDER BY CollectTime DESC) GROUP BY userid, boxid ORDER BY CollectTime DESC;rnEND IF;rnEND LABEL_PROC$$rnDELIMITER ;rn[/code]rn 论坛

连接管道

09-30

Problem Descriptionn老 Jack 有一片农田,以往几年都是靠天吃饭的。但是今年老天格外的不开眼,大旱。所以老 Jack 决定用管道将他的所有相邻的农田全部都串联起来,这样他就可以从远处引水过来进行灌溉了。当老 Jack 买完所有铺设在每块农田内部的管道的时候,老 Jack 遇到了新的难题,因为每一块农田的地势高度都不同,所以要想将两块农田的管道链接,老 Jack 就需要额外再购进跟这两块农田高度差相等长度的管道。nn现在给出老 Jack农田的数据,你需要告诉老 Jack 在保证所有农田全部可连通灌溉的情况下,最少还需要再购进多长的管道。另外,每块农田都是方形等大的,一块农田只能跟它上下左右四块相邻的农田相连通。n nnInputn第一行输入一个数字T(T≤10),代表输入的样例组数nn输入包含若干组测试数据,处理到文件结束。每组测试数据占若干行,第一行两个正整数 N,M(1≤N,M≤1000),代表老 Jack 有N行*M列个农田。接下来 N 行,每行 M 个数字,代表每块农田的高度,农田的高度不会超过100。数字之间用空格分隔。n nnOutputn对于每组测试数据输出两行:nn第一行输出:"Case #i:"。i代表第i组测试数据。nn第二行输出 1 个正整数,代表老 Jack 额外最少购进管道的长度。n nnSample Inputn2n4 3n9 12 4n7 8 56n32 32 43n21 12 12n2 3n34 56 56n12 23 4n nnSample OutputnCase #1:n82nCase #2:n74 问答

mysql 老是报this is incompatible with sql_mode=only_full_group_by

11-05

大大们,帮看下是怎么回事,本地MYSQL上运行没有问题,可是程序运行却报下面的错误。rnrn错误信息如下:rnExpression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'his.a.AttachPathId' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_byrn----------------------------------------------------------------------------------------------------------------rnSQL如下:rnSELECT mpd.MaterialId VE_MaterialShopId,mpd.MaterialName,mpd.TypeId,mpd.TypeName,mpd.Unit,rnmpd.Standard,mpd.OutPrice,mpd.ProductName,rncase when aa.Path is null then 'http://localhost:4321/UploadFolder/Msg/Error.jpg' else aa.Path end as Path rnfrom (SELECT m.MaterialId,m.Name MaterialName,m.TypeId,d.`Value` TypeName,rnm.Unit,m.Standard,m.OutPrice,p.Name ProductName From material m rnLEFT JOIN product p ON m.ProductId=p.ProductId rnLEFT JOIN dict d ON m.TypeId=d.DictId) mpd rnLEFT JOIN (SELECT a.AttachPathId,a.EntName,a.EntId,CONCAT('http://localhost:4321',a.Path) Path rnFROM attachpath a WHERE EntName='Material' GROUP BY EntId) aa rnON mpd.MaterialId=aa.EntId rn-------------------------------------------------------------------------------------------------------------------rn本地my.ini配置了跟服务器一样的 sql_mode ,本地运行没有问题,服务器用Navicat运行也没有问题,可是程序执行却报上面的错误。rnrnsql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'rn 论坛

没有更多推荐了,返回首页