mysql使用笔记

mysql_info
=========================================================
显示当前状态：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');