mysql 相关
(1)mysql大于、小于号
http://stackoverflow.com/questions/32042726/what-is-the-proper-syntax-for-the-less-than-equal-operator-in-mybatis-3
I show examples for the Greater Than/Equal Operator and the Less Than/Equal Operators:
ROWNUM >= 20
ROWNUM <= 20
Or, for readability, you can wrap them in CDATA
ROWNUM <![CDATA[ >= ]]> 20
ROWNUM <![CDATA[ <= ]]> 20
(2)mysql配置允许批量更新 allowMultiQueries=true
(3)主键冲突会进行文件名、状态更新
一个表中如果有 则更新 status字段 和 filename 字段,没有则 新增
INSERT INTO ec_file (updatetime, createtime, filesize, bucketname, filename, filetype , filepath )
VALUES ('2016-08-08 12:12:12','2016-09-08 12:12:12',0, "huowulian","aaaaa","33", "photo/2016/12/06/1_123455_A1_951357_01_20161206101536_3924.jpg")
ON DUPLICATE KEY UPDATE status = "1", filename="cccc"
(4)mysql插入生成主键:<insert id="addPackingInfo" useGeneratedKeys="true" keyProperty="pkid" parameterType="com.elevator.model.ECPackingInfo" >
(5)设置group_concat的max_len
set global group_concat_max_len=102400;
(6)查看group_concat_max_len的长度
select @@global.group_concat_max_len;
/*数据库中是dateTime类型,bean中是字符串类型;可类似下面的转换,不然会多出来个0*/
sql.append(" DATE_FORMAT(e.BEGINTIME, '%Y-%m-%d %h:%i:%s') as btA, ");
sql.append(" DATE_FORMAT(e.ENDTIME, '%Y-%m-%d %h:%i:%s') as etA, ");
DATE_FORMAT(e.BEGINTIME, '%Y-%m-%d %h:%i:%s') as btA,
DATE_FORMAT(e.ENDTIME, '%Y-%m-%d %h:%i:%s') as etA,
(7) show table status 查看表新增时间
更新某列,如果此列为空,则执行更新;如果此列不为空,在不更新
update fsmp_event set Subtype = if(Subtype = '', IATYPE , SUBTYPE)
(9)
mysql 1449 : The user specified as a definer ('root'@'%') does not exist 解决方法
mysql 1449 : The user specified as a definer ('****'@'%') does not exist 解决方法
我解决方案一般因为 ****未授权所有sql的权限
权限问题,授权 给 root 所有sql 权限
mysql> grant all privileges on *.* to root@"%" identified by ".";
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
如下图
(10)sql 查询 重复的 id
analyze table XXX
显示索引排序
SHOW INDEX FROM FSMP_COLLATERAL_RFID;
SELECT rfid, COUNT(rfid)
FROM fsmp_collateral_rfid
GROUP BY rfid
HAVING COUNT(rfid) > 1;