Mysql使用经验集锦

• 用户管理

# 创建数据库
create database 数据库名 default character set utf8 COLLATE utf8_bin;

# 创建用户
CREATE USER 'username'@'%' IDENTIFIED BY 'password';
CREATE USER 'username'@'IP' IDENTIFIED BY 'password';

# 用户授权
grant all privileges ON *.* TO 'username'@'%';
grant all privileges ON database.table TO 'username'@'%';
grant SELECT, INSERT, UPDATE, REFERENCES, DELETE, CREATE, DROP, ALTER, INDEX, CREATE VIEW, SHOW VIEW ON database.table TO 'username'@'%';

# 修改用户密码
set password for 'username'@'%'=password('password');
set password for 'username'@'IP'=password('password');
ALTER USER 'username'@'IP' IDENTIFIED BY 'password';

• Mysql数据库索引不区分大小写问题

Mysql数据库默认索引字段是不区分大小的，需要需要区分大小写，需要设置字段属性为Binary

ALTER TABLE t_report_month_sign MODIFY sms_sign varchar(64) binary NOT NULL DEFAULT '' COMMENT '签名';