mysql常用操作

一、时间日期处理

昨天0点：UNIX_TIMESTAMP(CAST(SYSDATE()AS DATE) - INTERVAL 1 DAY)

今天0点：UNIX_TIMESTAMP(CAST(SYSDATE()AS DATE))

时间戳-转日期
FROM_UNIXTIME('时间戳字段','%Y-%m-%d %H:%i:%s') // 参数2可以省略

转日期-时间戳
UNIX_TIMESTAMP('2015-04-29')

二、数据存储，物理文件恢复，命令备份(恢复)

1、数据存储

- myisam引擎：frm(表结构)、myd(表数据)、myi (表索引)、par(分区)
- innodb引擎：frm(表结构)、ibd(表数据和索引)、par(分区)

2、恢复

- myisam:1.删除表的物理数据文件（frm、myd、myi等） 2.复制表备份的frm、myd、myi、[#par#分区文件]等数据文件到mysql的datadir路径下即可（不需要重启mysql）
- innodb: 1.删除原表的物理数据文件（frm、myd、myi等） 2.复制表备份的frm、myd、myi、[#par#分区文件]等数据文件到mysql的datadir路径下即可（不需要重启mysql）

3、备份(恢复)

mysqldump -u root -p test person > D:\dump.sql

// 备份整个库

mysqldump -u username -p --databases dbname2 dbname2 > Backup.sql

// 备份所有库

 mysqldump -u root -p --all-databases > database_dump.sql

// 恢复

 - 1、mysql -u root -p [database_name] < dump.sql // 单库/多库恢复，如果是多库恢复，则不需要指定数据库
 - 2、use dbname;source dump.sql // 单库/单表恢复

// 远程复制

mysqldump -u root -p database_name  | mysql -h other-host.com -u root  -p database_name

三、库表查询

// 查看正在执行的操作

show full processlist

结果：

// 查询变量

show variables like '%log%'

// 查询表结构

desc 表名

// 查询建表语句

show create table tablename

// 查询建库语句

show create database dbname

// 查询表状态

show table status like 'part_order'

结果：

// 执行计划

explain sql 

explain partitions sql // 增加分区信息

三、例程

• 1、存储过程

CREATE DEFINER=`root`@`%` PROCEDURE `filldata`(IN `num` int,OUT `result` varchar(20))
BEGIN
	#Routine body goes here...
	DECLARE v int default 0;  
	WHILE v < num
	DO
	INSERT into list_order values(v,'test partition',CURRENT_TIMESTAMP,YEAR(ADDDATE('2019-01-01',INTERVAL cast(RAND(v)*10 as signed) MOD 4 YEAR)));
	set v = v + 1;
	END WHILE;
	set result = 'success';
END

调用

set @return='';
call filldata(800000,@return);
select @return

• 2、函数

CREATE DEFINER=`root`@`%` FUNCTION `testfunc`(`num` int) RETURNS varchar(20) CHARSET utf8mb4
BEGIN
	#Routine body goes here...

	RETURN 'success';
END

调用：

set @num=100;
select testfunc(@num)

三、函数

CAST 格式转换

1.字符串转浮点

cast('3.44' as decimal);

2.浮点转字符串
cast(‘3.44’ as char);

3.整形转字符串
cast(3 as char);

convert 格式转换

1.字符串转浮点
convert(‘3.44’,decimal);

2.整形转字符串
convert(‘3.44’,char);

3.整形转字符串
convert(3,char);

四、时区设置

[mysqld]
default-time_zone = ‘+8:00’

重启mysql