MySQL：修订间差异

2022年1月11日 (二) 06:56的版本

MySQL，一个开放源码的关系数据库管理系统，读作“My S-Q-L”。MySQL在过去由于性能高、成本低、可靠性好，已经成为最流行的开源数据库。但被甲骨文公司收购后，大幅调涨MySQL商业版的售价，且甲骨文公司不再支持另一个自由软件项目OpenSolaris的发展，MySQL的创始人麦克尔·维德纽斯以MySQL为基础，成立分支计划MariaDB。而原先一些使用MySQL的开源软件逐渐转向MariaDB或其它的数据库。例如维基百科已于2013年正式宣布将从MySQL迁移到MariaDB数据库。

简介

时间轴

2008年1月16日，Sun（太阳微系统）正式收购MySQL。
2009年4月20日，甲骨文公司宣布以每股9.50美元，74亿美元的总额收购Sun电脑公司。
2013年6月18日，甲骨文公司修改MySQL授权协议，移除了GPL。但随后有消息称这是一个bug。

安装

使用Docker安装

安装Docker，然后直接从官网镜像启动一个mysql容器，并设置root用户密码为123456。

docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql

了解更多 >> Docker Hub：MySQL

Ubuntu上安装

#更新软件源
sudo apt-get update 

#
sudo apt-get install mysql-server

了解更多 >> MySQL文档：使用APT仓库安装

可能遇到问题

主机只有1G内存，mysql却占用很大内存。

在终端输入top查看各程序cup和内存等占用率，然后输入M，使结果按内存大小排序，可以看到mysqld的占用率高。直接修改mysql服务器的配置文件my.cnf，如果使用mysql容器，先进入容器终端。

#进入某个容器
docker exec -it some-mysql bash
#在容器内安装vim
apt-get update
apt-get install vim

#打开并修改my.cnf文件
vim /etc/mysql/my.cnf
#在文件里加入如下配置
performance_schema_max_table_instances=400  
table_definition_cache=400  
table_open_cache=256
performance_schema = off

#重启mysql
service mysql stop
service mysql start
#输入exit，退出容器，然后重启该容器
docker restart some-mysql 
#用top命令查看内存占用率

错误：ERROR 1698 (28000): Access denied for user 'root'@'localhost'

客户端

名称	界面	描述
MySQL客户端程序	命令	免费。 MySQL客户端程序包含： `mysql` 命令行客户端 `mysqldump` 数据库备份程序 `mysqladmin` 服务器管理程序 ...
Navicat	图形	收费，简单易用，可用于多种数据库管理
SQLyog	图形	收费，简单易用
DBeaver	图形	免费，开源
MySQL Workbench	图形	免费，开源，MySQL官方工具
VS Code插件MySQL	图形	免费，开源。一款VS Code中的插件，作者cweijan。支持多种数据库，使用简单方便。

了解更多 >> MySQL 文档：MySQL程序 - 客户端程序

数据类型

概览

类型

类别	类型	描述	示例
字符串	CHAR(n)	固定长度字符串。	CHAR(10)
	VARCHAR(n)	可变长度字符	VARCHAR(150)
	TINYTEXT	字符串大对象
	TEXT	字符串大对象
	MEDIUMTEXT	字符串大对象
	LONGTEXT	字符串大对象
	ENUM	枚举
	SET	集合
二进制字符串	BINARY(n)	二进制	BINARY(10)
	VARBINARY(n)	可变长度二进制	VARBINARY(200)
	TINYBLOB	二进制大对象
	BLOB	二进制大对象	BLOB
	MEDIUMBLOB	二进制大对象
	LONGBLOB	二进制大对象
准确数值	TINYINT	微型整数，非标准SQL。占1字节，范围：无符号0~255，有符号-128~127。MySQL没有定义布尔类型（BOOL,BOOLEAN），使用TINYINT(1)表示，0表示false，非零表示true。
	SMALLINT	小型整数，非标准SQL。占2字节，	SMALLINT
	MEDIUMINT	中型整数，非标准SQL。占3字节，	MEDIUMINT
	INTEGER, INT	整数，占4字节，范围：无符号0~4294967295，有符号-2147483648~2147483647	INTEGER
	BIGINT	大型整数，非标准SQL。占8字节，范围：无符号0~ 2^64-1，有符号-2^63~2^63	BIGINT
	NUMERIC(p,s)	带固定精度和小数位数的数值。p（precision）表示整数和小数的总位数，s（scale）表示小数位数，p和s都可以省略。	NUMERIC(5,2)
	DECIMAL(p,s)	带固定精度和小数位数的数值，p（precision）表示整数和小数的总位数，s（scale）表示小数位数，p和s都可以省略。	DECIMAL(5,2)
近似数值	FLOAT(p)	浮点数	FLOAT(3)
	REAL	单精度浮点数	REAL
	DOUBLE	双精度	DOUBLE
日期时间	DATE[(p)]	日期。默认显示YYYY-MM-DD，范围1000-01-01~9999-12-31	DATE
	TIME(p)	时间	TIME
	DATETIME[(p)]	日期和时间。支持的范围是 '1000-01-01 00:00:00.000000'到 '9999-12-31 23:59:59.999999'。	DATE
	TIMESTAMP[(p)]	时间戳	TIMESTAMP(9)
	YEAR[(p)]	年。默认显示YYYY	YEAR
JSON	JSON	JSON
空间数据	geom	空间数据

转换

MySQL可以自动转换其他数据库的某些数据类型到MySQL的数据类型。可以在表定义时使用这些类型，如：

-- 定义一个表t1
CREATE TABLE t1 (id int3, is_ok BOOL);

-- 查看数据表定义可知，已经转换为MySQL类型
desc t1;

其他数据库类型	MySQL 类型
BOOL	TINYINT
BOOLEAN	TINYINT
CHARACTER VARYING(M)	VARCHAR(M)
FIXED	DECIMAL
FLOAT4	FLOAT
FLOAT8	DOUBLE
INT1	TINYINT
INT2	SMALLINT
INT3	MEDIUMINT
INT4	INT
INT8	BIGINT
LONG VARBINARY	MEDIUMBLOB
LONG VARCHAR	MEDIUMTEXT
LONG	MEDIUMTEXT
MIDDLEINT	MEDIUMINT
NUMERIC	DECIMAL

了解更多 >> MySQL 文档：使用来自其他数据库引擎的数据类型

SQL语句

基础语法

数据定义 DDL

数据库

名称	常用语句	描述	示例
创建数据库 create database	create database 数据库名； create database if not exists 数据库名； create database 数据库名 character set 字符集；	创建指定名称的数据库。`character set` 用于指定字符集，`SHOW CHARACTER SET`语句显示所有可用的字符集。创建一个数据库，MySQL会在数据目录下创建一个同名的目录。MySQL 对数据库的数量没有限制。	`CREATE DATABASE db1;` 创建名称为db1的数据库。 `CREATE DATABASE db2 CHARACTER SET utf8;` 创建数据库db2，并指定utf8字符集。
查看所有数据库 show databases	show databases; show databases like '模式';	显示MySQL中的数据库。	`show databases;` 显示所有数据库。 `show databases like 'my%';` 显示所有以my开头的数据库。
使用数据库 use	use 数据库;	当前语句执行的默认数据库。如`USE db1;`接下来所有操作默认在db1数据库中，`SELECT * FROM table1;`是从`db1.table1`表查询，要使用其他数据库的表需要指定数据库名如`db2.table1`。	`USE db2;` 切换到db2数据库。
查看当前数据库	select database();	查看当前语句执行的默认数据库。	select database();
查看数据库定义	show create database 数据库；	查看一个数据库的定义信息。	SHOW CREATE DATABASE db1;
修改数据库 alter database	alter database 数据库 character set 字符集； alter database 数据库 read only = {DEFAULT \| 0 \| 1}	修改数据库。 `character set` 修改字符集 `read only` 修改是否只读。8.0.22版本新增。	`ALTER DATABASE db1 CHARACTER SET utf8;` 修改数据库db1的字符集为utf8。 `ALTER DATABASE db1 READ ONLY = 1` 修改数据库db1为只读。
删除数据库 drop database	drop database 数据库; drop database if exists 数据库;	删除数据库。需要非常小心，所有该数据库及其所有表都将删除。if exists 可防止数据库不存在而报错。	DROP DATABASE db1;

数据表

名称	语句	描述	示例
创建表	create table 表名( 列名1 数据类型，列名2 数据类型 )； create table [if not exists] 表名( 列定义, ..., 表约束, ..., ) 表选项, ..., 分区选项, ...;	默认情况下，使用InnoDB存储引擎在当前默认数据库中创建表，MySQL对表的数量没有限制。常用列定义： 1.列名 2.数据类型，如`CHAR(20)`。 3.null或 not null，能不能为空值，默认可以`NULL`。 4.default 默认值，如`DEFAULT 0`， 8.0.13+默认值可以常量或用括号包裹(表达式) 5.AUTO_INCREMENT，自增长，每个表只能有一个自增长列且该列添加索引。6.COMMENT 注释，给列添加注释，最长1024 个字符。使用SHOW CREATE TABLE和SHOW FULL COLUMNS语句时会显示。 7.primary key，主键，一个表只有一个。该列必须约束有UNIQUE和NOT NULL，没有声明时MySQL会自动声明。 8.index 或 key，普通索引。 9.unique，唯一索引。 10.fulltext，全文索引。 11.foreign key，外键。 12.check，检查约束。常用表选项： 1.engine，存储引擎，默认InnoDB，设置其他如`ENGINE MEMORY` 2.CHARACTER SET，指定表的字符集 3.COMMENT，表的注释。常用分区选项 1.	CREATE TABLE book ( book_id INT UNSIGNED, book_name VARCHAR(50) );
快速创建空表	create table 新表名 like 旧表名;	根据一个表的定义创建一个空表。只能用于创建普通表，不能用于视图。	`CREATE TABLE t2 LIKE t1;` `CREATE TABLE IF NOT EXISTS t2 LIKE t1;`
使用SELECT创建表	create table 新表名 [as] select语句旧表名;	在CREATE TABLE语句的末尾添加SELECT语句，创建一个表。	CREATE TABLE t2 SELECT * FROM t1; CREATE TABLE t2 ( a INT, c INT ) SELECT * FROM FROM t1;
创建临时表	create temporary table 表名 ...;	临时表只在当前会话中可见，当会话关闭时自动删除。	`CREATE TEMPORARY TABLE t2 SELECT * FROM t1;`
查看所有表	show tables;	查看当前数据库中的所有表名。	`show tables;`
查看所有表详细信息	show table status;	与show tables类似，但提供了大量与表相关的信息，而不只是表名，如存储引擎、行数、创建时间和更新时间等。	`show table status;` `show table status like 't%';`
查看表中列的信息	desc 表名； describe 表名;	查看表中列的信息。	`DESC t1;`
查看表中列的信息	show columns from 表名; show [extended] [full] columns from 表名;	查看表中列的信息，比desc语句显示更多信息。extended，可以输出MySQL内部使用的隐藏信息。full，可以输出列排序规则，注释，当前用户权限。	`SHOW COLUMNS FROM t1;` `SHOW COLUMNS FROM db2.t1;` `SHOW FULL COLUMNS FROM t1 FROM db2;`
查看建表语句	show create table 表名;	查看表的create table语句,	`SHOW CREATE TABLE t1;`
修改表	alter table 表名选项；	修改表结构，如添加或删除列和索引、更改列数据类型等。	`ALTER TABLE t1 DROP COLUMN a, DROP COLUMN b;`

数据操作 DML

数据查询 DQL

数据控制 DCL

导入导出

导入

导入工具支持格式描述

LOAD DATA 语句

CSV等

SQL语句 LOAD DATA用于加载数据文本类型数据。示例：

当需要导入大量本地数据到mysql数据库时，可使用LOAD DATA LOCAL INFILE命令。但local_infile选项默认关闭状态，需要打开。以下以导入5千万行CSV文件为例。

-- mysql8.0以上版本，命令行登录数据库时，使用如下命令
-- mysql -u root -p --local-infile

-- 查看'local_infile'选项，并设置为ON
show variables like 'local_infile';
set global local_infile=on;

-- 建test表
CREATE TABLE test( 
  user_id INT, 
  name VARCHAR);

-- 导入本地csv文件，如果第一行有标题使用IGNORE 1 LINES忽略
LOAD DATA LOCAL INFILE 'C:\\Users\\TESTWORK\\Desktop\\test_data.csv'
INTO TABLE test
FIELDS TERMINATED BY ','
IGNORE 1 LINES;

mysqlimport CSV等 mysqlimport工具是LOAD DATA 语句的命令行接口

mysqldump SQL语句 mysqldump工具用于备份，可以导出和导入sql语句。

导出

常见问题

使用LOAD DATA INFILE加载CSV文件时，空值（NULL）会自动变为0。

空值和0不同，如在计算均值时，空值不会纳入计算，0值会增加个数。两种方法：

方法1：导入时使用NULLIF()函数进行预处理

-- 示例，对name列的空值进行预处理
-- 也可以全部例进行预处理，但最后一列好像不起作用?

LOAD DATA INFILE '/data/testdata.csv'
INTO TABLE book
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(id, @name, author) 
SET name = NULLIF(@name,'')
;

方法2：对csv文件进行预处理，空值处插入转义字符\N会自动转为NULL，如某行15, ,10，改为15,\N,10

LOAD DATA INFILE时报错。 
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

SHOW VARIABLES LIKE "secure_file_priv";

方法1：查询secure_file_priv值 SHOW VARIABLES LIKE "secure_file_priv"; ，如果存在目录，将文件移动到该安全目录即可导入。

方法2：配置secure_file_priv值，如ubuntu中可以编辑/etc/mysql/my.cnf配置文件，设置secure-file-priv= /data，重启mysql服务即可，docker可以直接重启容器。将文件放入/data目录即可导入。

资源

官网

教程

菜鸟教程：MySQL

网页

维基百科：MySQL

@@ 第433行： / 第433行： @@
 | 查看表的create table语句,
 | <code>SHOW CREATE TABLE t1;</code>
+|-
+| [https://dev.mysql.com/doc/refman/8.0/en/alter-table.html 修改表]
+| alter table 表名 选项；
+| 修改表结构，如添加或删除列和索引、更改列数据类型等。
+| <code>ALTER TABLE t1 DROP COLUMN a, DROP COLUMN b;</code>
 |-
 |}