select * from phbs_nurse_t order by CONVERT(nurse_name USING gbk)
- 序号转int 排序
select server_id from cardserver where game_id = 1 order by CAST(server_id as SIGNED) desc limit 10;
-
开启慢查询统计
show variables like 'slow_query%';
set global slow_query_log='ON';
set global long_query_time=5;
show variables like 'long_query_time%';
- 查看正在执行的SQL语句,干掉正在执行的SQL
show processlist;
kill 14568;
-
Navicat 开启事务
START transaction ;
COMMIT; rollback;
-
使用UUID
update 表名 set id = REPLACE(UUID(),'-','') where id is null or id ='';
-
快速创建一个备份表的结构
create table table_bak select * from table where 1=2
-
创建函数报错,log_bin_trunc...
# 1418 -This function has none of DETERMINISTIC,NOSQL,or READS SQL DATA in its declaration and binary logging is enabled(you *might* want to use the less safe log_bin_trust_function_creators variable)
set global log_bin_trust_function_creators=1;
-
临时表操作
CREATE TEMPORARY TABLE 表名_TMP
select * from 实例名.表名_;
-- 增加索引
ALTER TABLE 表名_TMP ADD INDEX index_lxid(`lxid`);
记录表 查询最新一条记录的更新人和更新时间
1 创建存储方法 SPLIT_STR
CREATE DEFINER=`root`@`%` FUNCTION `SPLIT_STR`(
x VARCHAR(255),
delim VARCHAR(12),
pos INT
) RETURNS varchar(255) CHARSET utf8
BEGIN
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
delim, '');
END
2
SELECT
id,
NAME,
GROUP_CONCAT( DISTINCT other_id ORDER BY id ASC ) otherId,
GROUP_CONCAT( DISTINCT other_name ORDER BY id ASC ) otherName,
max( modify_dt ) modifyDt,
SPLIT_STR ( max( concat( modify_dt, '&', modifier ) ), '&', 2 ) modifier
FROM
USER INNER JOIN user_log ON...
WHERE
USER.is_del = '0'
GROUP BY
id
SPLIT_STR :函数
BEGIN
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
delim, '');
END