MySQL基本使用

Mysql使用技巧

1. mysql5.5及以上，默认是innoDB引擎。mysql5.6以上，默认是innodb_file_per_table设置，即一个表一个文件。

2. select使用SQL_NO_CACHE避免查缓存

3. select使用SQL_CALC_FOUND_ROWS之后，可以使用SELECT FOUND_ROWS()有结果集总数

4. select count(*) innodb因为有并行事务，不能直接返回，而myisam可以直接返回。所以可以使用max(id)-min(id)来代替count(*)进行不精确统计。

5. 一个字符串类型字段，在拼接SQL语句时，一定得加上引号，否则索引不会使用。比如字段：target_id varchar(32)，如果查询写成select * from user where target_id=1234，就不会使用索引，因为在类型转换时，'1234 '和'1234'都可以表示1234。

6. 查看系统各种状态数据：

show status;

show slave status;

show engine innodb status; (查看各IO线程的状态，如果pending flushed很高的话，就要增加io线程数了；查看当前写入的Log sequence number)

show engines; （查看支持的存储引擎，MariaDB里使用此命令可以看到InnoDB实际对应的是Percona-XtraDB）

show charset; （查看支持的字符集）

show plugins;

show table status where Name='User'; （查看表占用磁盘信息）

show binary logs;

show master status;

show warnings;

show processlist;  （当前执行的sql）

describe Counter;  （或者show full columns from Counter）

show grants for root@localhost; （查询某用户的权限）

SHOW VARIABLES LIKE 'perf%'; （查询各种性能数据，需要开启性能统计功能performance_schema）

创建帐号

可以直接向mysql.user插入数据：

insert into mysql.user(Host,User,Password) values("BBBbbb","test",password("1234"));

注意之所以考虑这种方案，是因为：create user root@'BBBbbb' identified by '1234'; 会导致Host变成全小写bbbbbb。可能有某种方法让Mysql区分Host的大小写，以待日后补全。

使用create语法来创建：

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

接下来是授权：

1. 查看权限show grants for erp@localhost;

2. 添加权限grant all privileges on erp.* to erp@localhost identified by 'mypass' with grant option;

3. 使生效 flush privileges;

注意：with grant option表示有给其他用户授权的能力。

注意：erp.*是指erp数据库下的所有表，可以使用*.*来指明所有库的所有表。

注意：如果是erp@'%'，表示任何host。

注意：删除该用户使用drop user erp@localhost。

revoke select ON "test".* FROM 'root'@'%';  //去除select权限
revoke all ON "test".* FROM 'root'@'%';     //去除所有权限
grant select ON "test".* to 'root'@'%';     //添加select权限
grant all ON "test".* to 'root'@'%';        //添加所有权限
//所有权限包括：GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, 
//  INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, 
//  CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER

特殊的除了有all外，还有usage，表示创建一个没有任何权限的帐号，或者是保持当前权限不变，而修改with选项，如：

GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;

拷贝表

create table NewCounter like Counter;

insert into NewCounter select * from Counter;

select MD5(CONCAT(type, id)) from Counter limit 100;

update NewCounter set new_col = md5(concat(type, id));

存储MD5的优化

CREATE TABLE md5_tbl (md5_val BINARY(16), ...);

INSERT INTO md5_tbl (md5_val, ...) VALUES(UNHEX(MD5('abcdef')), ...);

binary更高效，对应字段不会进行字符集检查，比较也是纯粹的字节比较。

mysql> select BINARY 'a'='A';  //0

mysql> select BINARY 'a'='a '; //0

mysql> select 'a'='\a'; //1

mysql> select 'a'='A';  //1

mysql> select 'a'='a ';  //1

导出数据

导出数据有很多种情况，也有很多种方法。导表时使用mysqldump较好：

mysqldump -S /data/mysql.sock -uroot -p db user > user.data.sql

注意：如果只需导出表结构，使用--no-data；如果只导出数据，而不导表结构，使用--no-create-info；如果只需要特定的行，使用--where="sex='M' and age>10"；特别注意，默认会先执行drop操作的，使用--skip-add-drop-table，可以避免产生DROP TABLE IF EXISTS `user`语句；默认在插入数据时会锁表，使用 --skip-add-locks可以避免产生LOCK TABLES `user` WRITE语句。

特定数据导出，比如只需要导出某个表的某一个字段，可以在终端下使用\T /tmp/out.txt，可以使用：

nohup mysql -uroot -p1234 mydb -e 'select sign from Image into outfile "/tmp/image_sign.txt"' &

使用select into outfile结果更干净，速度更快，更适合大批量导出。它比如下方法好得多：

nohup mysql -uroot -p1234 mydb -e 'select sign from Image' > /tmp/image_sign.txt &

使用select into outfile导出某个表之后，可以使用load data infile来快速导入一个表的数据，其实数据文件就是一个csv的文本文件，如：

load data infile '/tmp/user.txt' into table User;

当然还可以更灵活的设置导入时每列对应的字段：

load data infile '/tmp/user.txt' into table User (id,username,password,login_time);

或者是额外指定某字段：

load data infile '/tmp/user.txt' into table User (id,username,password,login_time) set create_time=CURRENT_TIMESTAMP;

导入csv指定分隔符举例：

load data infile 'china_city.csv' into table china_region 
fields terminated by ',' 
lines terminated by '\n' 
(province,city,area,zip_code)