MySQL数据库常用问题总结备忘录

查询 MySQL 数据库大小

• 选择指定 information_schema 数据库

use information_schema;

• 查询整个数据库大小

select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES;

• 查询指定数据库大小

select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where table_schema='your_database_name';

• 查询指定数据库下某个表的大小

select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where table_schema='your_database_name' and table_name='your_table_name';

MySQL 数据库存中文字符乱码解决方法

• 修改 jdbc.url 配置

jdbc.url=jdbc:mysql://ip-address:port/your_database_name?useUnicode=true&characterEncoding=utf8

MySQL 新增用户, 并附指定权限

• 新增用户

create user ['username']@['localhost'] identified by ['password'];

• 附指定权限

grant all privileges on [database].[table] to ['username']@['localhost'];

• 删除用户

drop user ['username']@['localhost'];

MySQL 导入本地指定文件数据

-- 登录时开启导入本地文件功能。不开启这个功能，导入文件时会报“当前 mysql 版本不支持导入文件功能”的错误
mysql -u ${username} --local-infile=1 -p

-- 建表
create table ${tablename}(id int auto_increment not null, username varchar(20) not null, age int not null, primary key(id));

-- 导入 cvs 文件数据。指定编码为 utf-8 ，按照','隔开字段，'\n'换行符隔开一行，指定对应导入的字段，id 字段自增长
load data local infile '${filepath}' into table ${tablename} character set utf8 fields terminated by ',' lines terminated by '\n' (username, age) set id = NULL;

LOAD DATA INFILE Syntax

MySQL 导出表数据到指定文件

-- 导出表数据到指定文件
select * info outfile '${filepath}' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n' from ${tablename};

SELECT … INTO Syntax

重置 MySQL root 密码

# 停止 Mysql 服务
sudo service mysql stop
# 以 mysql 的安全模式启动服务
sudo mysqld_safe --skip-grant-tables&
# 直接登录 mysql 
mysql -uroot mysql
# 修改 root 密码
mysql > UPDATE user SET password=PASSWORD("password") WHERE user="root";
mysql > FLUSH PRIVILIGES;
# 重启 mysql 服务
sudo service mysql restart

MySQL 对于 zero date time 的处理

• Java 连接 Mysql 数据库，字段日期为 0 时，会抛出异常 java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column n to TIMESTAMP

# 解决方法为在配置 JDBC 链接时，添加 zeroDateTimeBehavior 属性来处理
# 1.zeroDateTimeBehavior=exception 默认值，抛出异常
# 2.zeroDateTimeBehavior=convertToNull 将值转为 NULL
# 3.zeroDateTimeBehavior=round 将值转为最近的正确值，即'0001-01-01'
jdbc:mysql://${mysql.serverUrl}?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull

MySQL 设置 max_allowed_packet

• 查看 max_allowed_packet 值

show VARIABLES like '%max_allowed_packet%';

• 修改 max_allowed_packet 值

SET GLOBAL max_allowed_packet=20 * 1024 * 1024

• 修改 /etc/mysql/my.cnf 文件中 [mysqld] 下的配置

max_allowed_packet=20M