MySQL 基本操作
安装使用
安装
Ubuntu 16.04
sudo apt-get install mysql-server
sudo mysql_secure_installation # 生产环境必备，安装一些安全策略
访问mysql
mysql -u root -p
其它参数：
-u username 登陆数据库所使用的数据库用户
-p password 使用密码登陆，如果没指定，则会在下面提示输入
-D database 此次登陆默认使用的数据库
基本操作
mysql> SHOW DATABASES; # 显示所有数据库
mysql> USE DATABASE; # 选择某个数据库
mysql> SHOW TABLES; # 显示当前数据库的所有表
mysql> SHOW VARIABLES LIKE '%char%'; # 显示数据库所用字符集
mysql> SHOW FULL COLUMNS FROM database.table; # 显示某个表的字符集
mysql> DESC TABLENAME; # 查看某个表的结构
mysql> SHOW GRANTS; # 查看当前用户权限
mysql> SHOW GRANTS FOR 'username'@'host'; # 查看某个用户的权限
mysql> CREATE USER 'username'@'host' IDENTIFIED BY 'password'; # 创建用户
# host使用%为允许从任意地方登录，如果只允许用户本地登录，改成localhost即可
mysql> GRANT privileges ON databasename.tablename TO 'username'@'host'; # 授权给某个用户
# privileges 权限，可选值为：SELECT, UPDATE, INSERT, DELETE, DROP, CREATE, ALL 等。
# databasename 数据库名，对所有数据库给予权限用 * 来表示。
# tablename 表名，对所有的表给予权限用 * 来表示。
# 示例：GRANT ALL ON test.* TO 'username'@'%';
# 用此种方式被授权的用户不能给其他用户授权，如需要被授权用户可以进行授权，则需
# GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES; # 应用更改的权限
mysql> REVOKE privilege ON databasename.tablename FROM 'username'@'host'; # 撤消用户权限
mysql> DROP USER 'username'@'host'; # 删除用户
# 修改数据库或者表字符集为utf8mb4
mysql> ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
mysql> ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# 索引操作
mysql> ALTER TABLE table_name ADD PRIMARY KEY (column_list); # 添加主键
mysql> ALTER TABLE table_name ADD UNIQUE index_name (column_list); # 添加唯一索引
mysql> ALTER TABLE table_name ADD INDEX index_name (column_list); # 添加普通索引
mysql> CREATE INDEX index_name ON table_name (column_list); # 添加普通索引
mysql> ALTER TABLE table_name RENAME INDEX index_name TO new_name # (MySQL 5.7+)更改索引名称
mysql> DROP INDEX index_name ON table_name; # 删除索引
mysql> SHOW INDEX FROM table_name; # 显示所有索引
# 重命名数据库
mysql> CREATE DATABASE new_db; # 首先，新创建一个数据库
mysql> RENAME TABLE old_db.table TO new_db.table; # 将旧数据库中表重命名到新库中
mysql> ; # 注意调整数据库中权限
$ mysql -u USERNAME -p PASSWORD -D DATABASE < sql_file # 执行某个sql文件
配置相关
将字符编码设置为UTF-8
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf # 老版本配置文件: /etc/mysql/my.cnf # [client] # default-character-set=utf8mb4 # # [mysqld] # character-set-server=utf8mb4 # collation-server=utf8mb4_unicode_ci # init_connect='SET NAMES utf8mb4' # skip-character-set-client-handshake = true # # [mysql] # default-character-set = utf8mb4让MySQL可以被远程访问
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf # 老版本配置文件: /etc/mysql/my.cnf # 注释掉: #bind-address = 127.0.0.1 # 再重启MySQL服务重置MySQL root 密码
# 重置密码: mysqladmin -u root password NEWPASS # 更改密码: mysqladmin -u root -p PASSWORD查看MySQL当前连接数
# 查看当前连接数： mysql> show status like 'Conn%'; # 查看当前客户端连接： mysql> show processlist;
基本使用
mysql> SELECT * FROM users LIMIT 5, 10; # 从users表中取出第5条开始的10条数据
mysql> SELECT * FROM users LIMIT 10 OFFSET 5; # 从users表中取出第5条开始的10条数据
mysql> SELECT user_id, age, name FROM my_user ORDER BY age DESC, user_id DESC # 多字段排序
# 多字段排序先以第一个字段进行排序，若第一个字段重复，则以第二个字段进行排序
mysql> SELECT * FROM users WHERE email REGEXP '^.*@qq\\.com$'; # 取出所有使用QQ邮箱的用户
mysql> SELECT concat(first_name, last_name) AS name FROM users;
# 从一个表中导入数据到另一个表中
mysql> INSERT INTO <table2> (fields) SELECT <fields> FROM <table1> [WHERE <conditions>];
查看SQL语句执行时间
mysql> select @@profiling; -- 查看分析开关是否打开
mysql> set profiling = 1; -- 打开分析开关
mysql> select count(*) ... -- 执行待测试的SQL语句
mysql> show profiles; -- 查看测试结果
mysql> show profile for query 1; -- 查看第一条执行语句执行具体时间花费
问题与解决办法
1) MySQL 在导入大量数据时缓慢的问题
MySQL导出的SQL语句在导入时有可能会非常非常慢，经历过导入仅45万条记录，竟用了近3个小时。在导出时合理使用几个参数，可以大大加快导 入的速度。
| 参数 | 描述 |
|---|---|
| -e | 使用包括几个VALUES列表的多行INSERT语法; |
| –max_allowed_packet=XXX | 客户端/服务器之间通信的缓存区的最大大小; |
| –net_buffer_length=XXX | TCP/IP和套接字通信缓冲区大小,创建长度达net_buffer_length的行。 |
注意：max_allowed_packet和net_buffer_length不能比目标数据库的设定数值大，否则可能出错。
首先确定目标库的参数值
mysql> show variables like 'max_allowed_packet';
mysql> show variables like 'net_buffer_length';
根据参数值书写mysqldump命令从源数据库导出数据，如：
mysqldump -u root -p -e --max_allowed_packet=1048576 --net_buffer_length=16384 db1 > backup.sql
# 使用-B(--databases)选项备份多个数据库
mysqldump -uroot -p -B db1 db2 db3 > db.sql
然后可以直接进行导入:
mysql -u root -p < ./backup.sql
2) MySQL 中文匹配
使用LIKE时，中文匹配的问题
TODO
3) 字符集冲突
在MySQL中不同的字符集格式不能进行=等类似操作。
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='
解决办法：
更新数据库字符集
ALTER DATABASE <database> DEFAULT COLLATE utf8mb4_unicode_ci;更新数据库表字符集
ALTER TABLE <table> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;更新某一行字符集
ALTER TABLE <table> MODIFY COLUMN <col> VARCHAR(255) COLLATE utf8mb4_unicode_ci;