查看“MySQL”的源代码

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

{{了解更多
|[https://hub.docker.com/_/mysql Docker Hub：MySQL]
}}

====Ubuntu上安装====
<syntaxhighlight lang="bash">
#更新软件源
sudo apt-get update 

#
sudo apt-get install mysql-server  
</syntaxhighlight>

{{了解更多|[https://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/ MySQL文档：使用APT仓库安装]}}
====可能遇到问题====
*主机只有1G内存，mysql却占用很大内存。
在终端输入top查看各程序cup和内存等占用率，然后输入M，使结果按内存大小排序，可以看到mysqld的占用率高。直接修改mysql服务器的配置文件my.cnf，如果使用mysql容器，先进入容器终端。
<syntaxhighlight lang="bash">
#进入某个容器
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命令查看内存占用率
</syntaxhighlight>

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


==客户端==
{| class="wikitable"  style="width: 100%;
! 名称
! 界面
! 描述
|-
| MySQL客户端程序
| 命令
| 免费。<br \>MySQL客户端程序包含：<br \><code>mysql</code> 命令行客户端 <br \><code>mysqldump</code> 数据库备份程序 <br \><code>mysqladmin</code> 服务器管理程序 <br \>...
|- 
| [[Navicat]]
| 图形
| 收费，简单易用，可用于多种数据库管理
|- 
| SQLyog
| 图形
| 收费，简单易用
|- 
| DBeaver
| 图形
| 免费，开源
|- 
| MySQL Workbench
| 图形
| 免费，开源，MySQL官方工具
|- 
| [[VS Code]]插件MySQL
| 图形
| 免费，开源。一款[[VS Code]]中的插件，作者cweijan。支持多种数据库，使用简单方便。
|}
{{了解更多
|[https://dev.mysql.com/doc/refman/8.0/en/programs-client.html MySQL 文档：MySQL程序 - 客户端程序]
}}
==数据类型==
=== 概览 ===

===类型===
{| class="wikitable"  style="width: 100%;
! 类别
! 类型
! 描述
! 示例
|-
| rowspan="8"|[https://dev.mysql.com/doc/refman/8.0/en/string-types.html 字符串]
| CHAR(n)
| 固定长度字符串。
| CHAR(10)
|- 
| VARCHAR(n)
| 可变长度字符
| VARCHAR(150)
|- 
| TINYTEXT
| 字符串大对象
| 
|- 
| TEXT
| 字符串大对象
| 
|- 
| MEDIUMTEXT
| 字符串大对象
| 
|- 
| LONGTEXT
| 字符串大对象
| 
|- 
| ENUM
| 枚举
| 
|- 
| SET
| 集合
| 
|- 
| rowspan="6"|二进制字符串
| BINARY(n)
| 二进制
| BINARY(10)
|-
| VARBINARY(n)
| 可变长度二进制
| VARBINARY(200)
|-
| TINYBLOB
| 二进制大对象
|
|-
| BLOB
| 二进制大对象
| BLOB
|-
| MEDIUMBLOB
| 二进制大对象
| 
|-
| LONGBLOB
| 二进制大对象
|
|-
| rowspan="7"|[https://dev.mysql.com/doc/refman/8.0/en/numeric-types.html 准确数值]
| 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)
|-
| rowspan="3"|[https://dev.mysql.com/doc/refman/8.0/en/numeric-types.html 近似数值]
| FLOAT(p)
| 浮点数
| FLOAT(3)
|-
| REAL
| 单精度浮点数
| REAL
|-
| DOUBLE
| 双精度
| DOUBLE
|-
| rowspan="5"|[https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html 日期时间]
| 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
|-
| rowspan="1"|JSON
| JSON
| JSON
| 
|-
| rowspan="1"|空间数据
| geom
| 空间数据
| 
|}


{{了解更多
|[https://dev.mysql.com/doc/refman/8.0/en/data-types.html MySQL 文档：数据类型]
|[https://dev.mysql.com/doc/refman/8.0/en/other-vendor-data-types.html MySQL 文档：使用来自其他数据库引擎的数据类型]
}}

===转换===
MySQL可以自动转换其他数据库的某些数据类型到MySQL的数据类型。可以在表定义时使用这些类型，如：
<syntaxhighlight lang="SQL">
-- 定义一个表t1
CREATE TABLE t1 (id int3, is_ok BOOL);

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

{| class="wikitable" 
|-
! 其他数据库类型
! 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
|}
{{了解更多
|[https://dev.mysql.com/doc/refman/8.0/en/other-vendor-data-types.html MySQL 文档：使用来自其他数据库引擎的数据类型]
}}

== SQL语句 ==
=== 基础语法 ===
=== 数据定义 DDL ===
==== 数据库 ====
{| class="wikitable" 
|-
! 名称
! 常用语句
! 描述
! 示例
|-
| 创建数据库 <br \>[https://dev.mysql.com/doc/refman/8.0/en/create-database.html create database]
|create database 数据库名； <br \><br \>create database if not exists 数据库名； <br \><br \>create database 数据库名 character set 字符集；
| 创建指定名称的数据库。<code>character set</code> 用于指定字符集，<code>SHOW CHARACTER SET</code>语句显示所有可用的字符集。 <br \><br \>创建一个数据库，MySQL会在数据目录下创建一个同名的目录。MySQL 对数据库的数量没有限制。
| <code>CREATE DATABASE db1; </code> 创建名称为db1的数据库。  <br \><code>CREATE DATABASE db2 CHARACTER SET utf8;</code> 创建数据库db2，并指定utf8字符集。
|-
| 查看所有数据库 <br \>[https://dev.mysql.com/doc/refman/8.0/en/show-databases.html show databases]
| show databases;  <br \><br \><nowiki>show databases like '模式';</nowiki>
| 显示MySQL中的数据库。
| <code>show databases; </code> 显示所有数据库。 <br \><code>show databases like 'my%';</code> 显示所有以my开头的数据库。
|-
| 使用数据库 <br \>[https://dev.mysql.com/doc/refman/8.0/en/use.html use]
| use 数据库;
| 当前语句执行的默认数据库。<br \><br \>如<code>USE db1;</code>接下来所有操作默认在db1数据库中，<code>SELECT * FROM table1;</code>是从<code>db1.table1</code>表查询，要使用其他数据库的表需要指定数据库名如<code>db2.table1</code>。
| <code>USE db2;</code> 切换到db2数据库。
|-
| 查看当前数据库
| select database();
| 查看当前语句执行的默认数据库。
| select database();
|-
| 查看数据库定义
| show create database 数据库；
| 查看一个数据库的定义信息。
| SHOW CREATE DATABASE db1;
|-
| 修改数据库 <br \>[https://dev.mysql.com/doc/refman/8.0/en/alter-database.html alter database]
| alter database 数据库 character set 字符集； <br \><br \><nowiki>alter database 数据库 read only = {DEFAULT | 0 | 1}</nowiki>
| 修改数据库。<br \><br \><code>character set</code> 修改字符集<br \><code>read only</code> 修改是否只读。8.0.22版本新增。
| <code>ALTER DATABASE db1 CHARACTER SET utf8;</code> 修改数据库db1的字符集为utf8。 <br \><code>ALTER DATABASE db1 READ ONLY = 1</code> 修改数据库db1为只读。
|-
| 删除数据库 <br \>[https://dev.mysql.com/doc/refman/8.0/en/drop-database.html drop database]
| drop database 数据库; <br \>drop database if exists 数据库; 
| 删除数据库。需要非常小心，所有该数据库及其所有表都将删除。if exists 可防止数据库不存在而报错。
| DROP DATABASE db1;
|}
{{了解更多
|[https://dev.mysql.com/doc/refman/8.0/en/sql-data-definition-statements.html MySQL 文档：数据定义语句]
|[https://dev.mysql.com/doc/refman/8.0/en/database-use.html MySQL 文档：教程 - 创建和使用数据库]
}}

==== 数据表 ====
{| class="wikitable" 
|-
! 名称
! 语句
! 描述
! 示例
|-
| [https://dev.mysql.com/doc/refman/8.0/en/create-table.html 创建表]
| <syntaxhighlight lang="sql">
create table 表名(
列名1 数据类型， 
列名2 数据类型 
)； </syntaxhighlight><syntaxhighlight lang="sql">
create table  [if not exists] 表名(
  列定义,
  ...,
  表约束,
  ...,
)
表选项,
...,
分区选项,
...;
</syntaxhighlight>
| 默认情况下，使用InnoDB存储引擎在当前默认数据库中创建表，MySQL对表的数量没有限制。 <br \><br \>常用[https://dev.mysql.com/doc/refman/8.0/en/create-table.html#create-table-types-attributes 列定义]：<br \>1.列名 <br \>2.数据类型，如<code>CHAR(20)</code>。<br \>3.null或 not null，能不能为空值，默认可以<code>NULL</code>。 <br \>4.default 默认值，如<code>DEFAULT 0</code>， 8.0.13+默认值可以常量或用括号包裹(表达式) <br \>5.AUTO_INCREMENT，自增长，每个表只能有一个自增长列且该列添加索引。6.COMMENT 注释，给列添加注释，最长1024 个字符。使用SHOW CREATE TABLE和SHOW FULL COLUMNS语句时会显示。<br \>7.primary key，主键，一个表只有一个。该列必须约束有UNIQUE和NOT NULL，没有声明时MySQL会自动声明。<br \>8.index 或 key，普通索引。<br \>9.unique，唯一索引。<br \>10.fulltext，全文索引。<br \>11.foreign key，外键。<br \>12.check，检查约束。 <br \><br \>常用[https://dev.mysql.com/doc/refman/8.0/en/create-table.html#create-table-options 表选项]：<br \>1.engine，存储引擎，默认InnoDB，设置其他如<code>ENGINE MEMORY</code><br \>2.CHARACTER SET，指定表的字符集 <br \>3.COMMENT，表的注释。 <br \><br \>常用[https://dev.mysql.com/doc/refman/8.0/en/create-table.html#create-table-partitioning 分区选项]<br \>1.
| <syntaxhighlight lang="sql">
CREATE TABLE book (
book_id INT UNSIGNED,
book_name VARCHAR(50)
); </syntaxhighlight>
|-
| [https://dev.mysql.com/doc/refman/8.0/en/create-table-like.html 快速创建空表]
| create table 新表名 like 旧表名;
| 根据一个表的定义创建一个空表。只能用于创建普通表，不能用于视图。
| <code>CREATE TABLE t2 LIKE t1; </code> <br \><code>CREATE TABLE IF NOT EXISTS t2 LIKE t1; </code> 
|-
| [https://dev.mysql.com/doc/refman/8.0/en/create-table-select.html 使用SELECT创建表]
| <nowiki>create table 新表名 [as] select语句 旧表名;</nowiki>
| 在CREATE TABLE语句的末尾添加SELECT语句，创建一个表。
|<syntaxhighlight lang="sql">
CREATE TABLE t2 SELECT * FROM t1;
</syntaxhighlight>
<syntaxhighlight lang="sql">
CREATE TABLE t2 (
a INT, 
c INT )
SELECT * FROM FROM t1;
</syntaxhighlight>
|-
| [https://dev.mysql.com/doc/refman/8.0/en/create-temporary-table.html 创建临时表]
| create temporary table 表名 ...; 
| 临时表只在当前会话中可见，当会话关闭时自动删除。
| <code>CREATE TEMPORARY TABLE t2 SELECT * FROM t1;</code> 
|-
| [https://dev.mysql.com/doc/refman/8.0/en/show-tables.html 查看所有表]
| show tables;
| 查看当前数据库中的所有表名。
| <code>show tables;</code>
|-
| [https://dev.mysql.com/doc/refman/8.0/en/show-table-status.html 查看所有表详细信息]
| show table status;
| 与show tables类似，但提供了大量与表相关的信息，而不只是表名，如存储引擎、行数、创建时间和更新时间等。
| <code>show table status;</code> <br \><code>show table status like 't%';</code>
|-
| [https://dev.mysql.com/doc/refman/8.0/en/describe.html 查看表中列的信息]
| desc 表名；<br \>describe 表名;
| 查看表中列的信息。
| <code>DESC t1;</code>
|-
| [https://dev.mysql.com/doc/refman/8.0/en/show-columns.html 查看表中列的信息]
| show columns from 表名; <br \>show [extended] [full] columns from 表名;
| 查看表中列的信息，比desc语句显示更多信息。extended，可以输出MySQL内部使用的隐藏信息。full，可以输出列排序规则，注释，当前用户权限。
| <code>SHOW COLUMNS FROM t1;</code> <br \><code>SHOW COLUMNS FROM db2.t1;</code> <br \> <code>SHOW FULL COLUMNS FROM t1 FROM db2;</code> 
|-
| [https://dev.mysql.com/doc/refman/8.0/en/show-create-table.html 查看建表语句]
| show create table 表名;
| 查看表的create table语句,
| <code>SHOW CREATE TABLE t1;</code>
|-
| [https://dev.mysql.com/doc/refman/8.0/en/rename-table.html 修改表名]
| rename table 旧表名 to 新表名；
| 修改表名
| <code>RENAME TABLE t1 TO new_t1;</code> <br \><code>RENAME TABLE old_t1 TO new_t1, old_t2 to new_t2;</code>
|-
| [https://dev.mysql.com/doc/refman/8.0/en/alter-table.html#alter-table-add-drop-column 增加列和删除列]
| <syntaxhighlight lang="sql">
alter table 表名 add [column] 列名 数据类型；
</syntaxhighlight><syntaxhighlight lang="sql">
alter table 表名 drop [column] 列名；
</syntaxhighlight>
| 添加列add，删除列drop，column关键字可以省略。
| <code>ALTER TABLE t1 DROP name VARCHAR(20);</code> 向t1表增加name列 <br \><code>ALTER TABLE t1 DROP COLUMN a, DROP COLUMN b;</code>
|-
| [https://dev.mysql.com/doc/refman/8.0/en/alter-table.html#alter-table-redefine-column 修改列名称或定义]
| alter table 表名 change 旧列名 新列名 列定义;
| 可以同时修改列名或列定义
| <code>ALTER TABLE t1 CHANGE a c INT NOT NULL;</code> 修改a列名称为c，并修改列定义。
|-
| [https://dev.mysql.com/doc/refman/8.0/en/alter-table.html#alter-table-redefine-column 修改列定义]
| alter table 表名 modify 旧列名 列定义;
| 只修改列定义
| <code>ALTER TABLE t1 MODIFY a INT NOT NULL;</code> 
|-
| [https://dev.mysql.com/doc/refman/8.0/en/alter-table.html#alter-table-redefine-column 修改列名]
| alter table 表名 rename column 旧列名 to 新列名;
| 只修改列名称
| <code>ALTER TABLE t1 RENAME COLUMN a TO c;</code>
|-
| [https://dev.mysql.com/doc/refman/8.0/en/alter-table.html#alter-table-character-set 修改字符集]
|<syntaxhighlight lang="sql">
alter table 表名 convert to character set 字符集；
</syntaxhighlight> <syntaxhighlight lang="sql">
alter table 表名 modify 列名 数据类型 character set 字符集；
</syntaxhighlight> <syntaxhighlight lang="sql">
alter table 表名 [default] character set 字符集；
</syntaxhighlight>
| convert to用于修改所有列字符集。<br \>modify或change修改单列字符集。<br \>default仅修改默认字符集，对后面添加列有效。
|<code>ALTER TABLE t1 MODIFY a TEXT CHARACTER SET utf8;</code>
|}
{{了解更多
|[https://dev.mysql.com/doc/refman/8.0/en/sql-data-definition-statements.html MySQL 文档：数据定义语句]
|[https://dev.mysql.com/doc/refman/8.0/en/database-use.html MySQL 文档：教程 - 创建和使用数据库]
}}
=== 数据操作 DML ===
=== 数据查询 DQL ===
=== 数据控制 DCL === 

== 导入导出 ==
=== 导入 ===
{| class="wikitable"  style="width: 100%;
! 导入工具
! 支持格式
! 描述
|-
| <code>LOAD DATA</code> 语句
| CSV等
|  SQL语句 <code>LOAD DATA</code>用于加载数据文本类型数据。示例：<br \><br \>当需要导入大量本地数据到mysql数据库时，可使用<code>LOAD DATA LOCAL INFILE</code>命令。但</code>local_infile</code>选项默认关闭状态，需要打开。以下以导入5千万行CSV文件为例。
<syntaxhighlight lang="SQL">
-- 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;
</syntaxhighlight>
|- 
| [https://dev.mysql.com/doc/refman/8.0/en/mysqlimport.html mysqlimport] 
| CSV等
| mysqlimport工具是LOAD DATA 语句的命令行接口
|- 
| [https://dev.mysql.com/doc/refman/8.0/en/using-mysqldump.html mysqldump]
| SQL语句
| mysqldump工具用于备份，可以导出和导入sql语句。
|}
=== 导出 ===


=== 常见问题 ===
 使用LOAD DATA INFILE加载CSV文件时，空值（NULL）会自动变为0。
空值和0不同，如在计算均值时，空值不会纳入计算，0值会增加个数。两种方法：
*方法1：导入时使用<code>NULLIF()</code>函数进行预处理
<syntaxhighlight lang="SQL">
-- 示例，对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,'')
;
</syntaxhighlight>

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

{{了解更多
|[https://stackoverflow.com/questions/2675323/mysql-load-null-values-from-csv-data  stackoverflow：MySQL从CSV数据加载NULL值]
|[https://dev.mysql.com/doc/refman/8.0/en/problems-with-null.html MySQL 文档：空值的问题]
|[https://dev.mysql.com/doc/refman/8.0/en/load-data.html MySQL 文档：Load Data 语句]
}}

 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值 <code>SHOW VARIABLES LIKE "secure_file_priv";</code> ，如果存在目录，将文件移动到该安全目录即可导入。

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

{{了解更多
|[https://stackoverflow.com/questions/34102562/mysql-error-1290-hy000-secure-file-priv-option stackoverflow：MySQL ERROR 1290 (HY000) --secure-file-priv option]
}}

==资源==
===官网===
*[https://www.mysql.com/ MySQL 官网]
*[https://www.mysql.com/cn/ MySQL 中文官网]
*[https://dev.mysql.com/doc/ MySQL 文档]

===教程===
*[https://www.runoob.com/mysql/mysql-tutorial.html 菜鸟教程：MySQL]

===网页===
*[https://zh.wikipedia.org/wiki/MySQL 维基百科：MySQL]

[[分类:数据库]]