mysql中的Tips

持续更新…

数据库备份与还原

# 备份
mysqldump -uroot -h127.0.0.1 -P3301 -p 123456 db_test.table_test > /tmp/table_test_bak.sql
# 还原
# 还原要先有库，所以先创建库
CREAT DATABASE db_test DEFAULT CHARSET='utf8';
mysqldump -uroot -h127.0.0.1 -P3301 -p123456 db_test < /tmp/table_test_bak.sql

将csv中数据存入数据库

LOAD DATA LOCAL INFILE "/tmp/test.csv" REPLACE INTO TABLE test_table FIELDS TERMINATED BY "," LINES TERMINATED BY "\n";

数据库表中某字段跟随插入时间更新

CREATE TABLE table_test (
 	name varchar(20) NOT NULL,
 	update_time DEFAULT TIMESTAMP on current_timestamp
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT '测试表';

复制表结构到一个新表中

CREATE TABLE newuser LIKE user;
create table select * from --  要求目标表不存在，因为在插入时会自动创建
insert into table select from -- 要求目标表存在，相当于复制插入
create table newuser as select * from user;  -- 复制表结构和数据
create table newuser as select * from user where 1=0;  -- 只复制表结构
insert into newuser select * from user ;

mysql中的FROM_UNIXTIME()函数和UNIX_TIMESTAMP()函数

FROM_UNIXTIME(unix_timestamp,format)
-- 参数unix_timestamp 时间戳 可以用数据库里的存储时间数据的字段
-- 参数format　　要转化的格式 比如“”%Y-%m-%d“” 这样格式化之后的时间就是 2017-11-30

SELECT FROM_UNIXTIME(day * 86400) AS date FROM test_table;

UNIX_TIMESTAMP(date)
-- 其中date可以是一个DATE字符串，一个DATETIME字符串，一个TIMESTAMP或者一个当地时间的YYMMDD或YYYMMDD格式的数字

SELECT UNIX_TIMESTAMP(date) AS time_stamp FROM test_table;

SELECT FROM_UNIXTIME(day * 86400) AS date FROM test_table WHERE day >= (UNIX_TIMESTAMP('2019-01-01 00：00：00') / 86400) AND day <= (UNIX_TIMESTAMP('2019-12-31 00:00:00’) 86400)

mysql解除死锁的两种方法

-- 1.查询是否锁表
show OPEN TABLES where In_use > 0;

-- 2.查询进程（如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程）
show processlist

-- 3.杀死进程id（就是上面命令的id列）
kill id

-- 1.查看下在锁的事务 
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

-- 2.杀死进程id（就是上面命令的trx_mysql_thread_id列）
kill 线程ID

-- 其它关于查看死锁的命令：
-- 1：查看当前的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

-- 2：查看当前锁定的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

--3：查看当前等锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;