MySQL 基本操作

· Read in about 3 min · (503 Words)
dev work

安装使用

安装

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> CREATE DATABASE newdb;  # 首先,新创建一个数据库
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;
    

相关资料

Comments