=========================================================
显示当前状态:show STATUS
查询超时: show variables like '%timeout%';
查询是否锁表 show OPEN TABLES where In_use > 0;
查询进程 show processlist
查询到相对应的进程===然后 kill id
查看正在锁的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
查看等待锁的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
字符串转毫秒: UNIX_TIMESTAMP('2011-05-31 23:59:59')*1000
字符串转时间: str_to_date('2008-4-2 15:3:28','%Y-%m-%d %H:%i:%s');
时间转毫秒: date_format(日期字段,’%Y-%m-%d’)
毫秒转时间戳: FROM_UNIXTIME(t.createtime/1000,'%Y-%m-%d %h:%i:%s')
示例:select FROM_UNIXTIME(t.createtime/1000,'%Y-%m-%d %h:%i:%s') as date,t.* from dealorder t where t.dealstatus =1
and t.customerid in (SELECT t.id from user t where t.clientmanageid ='395665593088464') and t.createtime>UNIX_TIMESTAMP('2016-06-01 01:01:01')*1000 ;
查询指定数据库ksudi_star每个表的条数:
use information_schema;SET group_concat_max_len = 100000;
SELECT concat('select tn,ct from (',substring(a.ss,1, locate(', count(*)',a.ss)-1),' as tn, count(*) as ct',substring(a.ss, locate(', count(*)',a.ss)+10,LENGTH(a.ss)-locate(', count(*)',a.ss)-19) ,') t ORDER BY t.ct desc;' ) INTO @str from
(select GROUP_CONCAT(t.qs SEPARATOR ' \n') ss from(select concat('select "',TABLE_name,'", count(*) from ',TABLE_SCHEMA,'.',TABLE_name,' union all') qs from tables where TABLE_SCHEMA='db_name') t ) a;
PREPARE stmt1 FROM @str;
EXECUTE stmt1 ;
DEALLOCATE stmt1 ;
批量更新:
update express t2 set t2.sender='浦东02站点',t2.sendaddress='福山路200号', t2.receiveaddress='田东路16号',t2.coordinates='121.534676,31.236822', t2.`status`=4, t2.orderstatus=1,
t2.currentsiteid=304068182959092, t2.nextsiteid=304068182959092, t2.createsiteid=304068182959092, t2.picksiteid=304068182959092,t2.sendsiteid=311772800569393,
t2.expresscompanyid=304153224466660,t2.cityid=1,agingtype=1
where t2.id in (SELECT t3.id from (SELECT t1.id from express t1 ORDER BY t1.id asc LIMIT 0, 10000 ) t3) ;
批量删除:
DELETE t2 from assign t2
where t2.expressid in (SELECT t3.id from (SELECT t1.id from express t1 ORDER BY t1.id asc LIMIT 0, 10000 ) t3) ;
递归查询:
SELECT T2.id, T2.name FROM test1 T2
where T2.id in
(select t1._id from (
SELECT @r AS _id,(SELECT @r := parent_id FROM test1 WHERE id = _id) AS parent_id
FROM (SELECT @r := 6) vars, test1 h
WHERE @r <> 0 and name<3) T1 );
数据示例:
CREATE TABLE `test1` (
`id` varchar(255) NOT NULL DEFAULT '',
`aa` varchar(255) NOT NULL DEFAULT '',
`bb` varchar(255) DEFAULT NULL,
`parent_id` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `test1` VALUES ('1', '1', '1', '0', '1');
INSERT INTO `test1` VALUES ('2', '2', '2', '1', '2');
INSERT INTO `test1` VALUES ('3', '3', '3', '2', '3');
INSERT INTO `test1` VALUES ('4', '4', '4', '3', '4');
INSERT INTO `test1` VALUES ('5', '5', '5', '4', '4');
INSERT INTO `test1` VALUES ('6', '6', '6', '5', '5');
INSERT INTO `test1` VALUES ('7', '7', '7', '6', '6');
INSERT INTO `test1` VALUES ('8', '8', '8', '7', '7');