MySQL学习笔记

概述

记录MySQL零零散散的知识点。

数据类型

1. 整数类型
   可以使用以下几种整数类型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别使用8、16、24、32、64位存储空间，它们可以存储的值范围是-2^(N-1)到2(N-1)-1，其中N是存储空间的位数。MySQL可以为整数类型指定宽度，如INT(11)，但是对大多数应用是没意义的，因为它并不会限制值的范围，只是规定MySQL的一些交互工具显示字符的个数，对于存储和计算来说，INT(1)和INT(11)是相同的。
2. 实数类型
   实数类型不仅用于存储带小数的数据，还可以用DECIMAL存储比BIGINT还大的整数。FLOAT和DOUBLE支持使用标准的浮点运算进行近似计算，DECIMAL用于存储精确的小数。FLOAT使用4个字节存储数据，DOUBLE使用8个字节存储数据，MySQL5.0和更高的版本中DECIMAL允许最多65个数字。
3. 字符串类型
   VARCHAR用于存储可变长字符串，它比定长类型更节省空间，因为它仅使用必要的空间。它需要额外的空间存储字节长度，如果列最大长度小于或等于255使用一个字节，否则使用两个字节。CHAR是定长的，长度不够会以空格填充，适用于存储较短的数据，更适用于所有列值都接近同一个长度的情况。
4. BLOB使用二进制方式存储很大数据量的字符串。
5. TEXT使用字符方式存储很大数据量的字符串。
6. 日期和时间类型
   DATETIME能保存的范围值从1001年到9999年，精度为秒，它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关，使用8个字节的存储空间。
   TIMESTAMP保存从1970年1月1日午夜(格林尼治标准时间)以来的秒数，它使用4个字节的存储空间，因此时间范围比DATETIME小得多，只能表示从1970年到2038年，并且它显示的值依赖于时区，所以在使用这个类型的时候，需要正确设置机器时区。它的列值默认是非空的，并且默认情况下具有自动更新的特性。
7. 位数据类型
   在MySQL5.0之前，BIT和TINYINT是同义的，但在5.0+版本后不再是。BIT(1)定义一个包含单个位的字段，BIT(2)存储两个位，BIT最大长度为64个位。MySQL把BIT当作字符串类型，而不是数字类型，将检索BIT(1)的值时，结果是一个包含二进制0或1值的字符串，而不是ASCII码的“0”或“1”。

datetime和timestamp

MySQL中有三种日期类型：

• date：yyyy-MM-dd
• datetime：日期时间类型
• timestamp：时间戳类型，保存年-月-日 时-分-秒

datetime

1. 允许为空值，可以自定义值，系统不会自动修改其值；
2. 不可以设定默认值，所以在不允许为空值的情况下，必须手动指定 datetime 字段的值才可以成功插入数据；
3. 虽然不可以设定默认值，但是可以在指定 datetime 字段的值的时候使用 now() 变量来自动插入系统的当前时间；
   结论：datetime 类型适合用来记录数据的原始的创建时间，因为无论你怎么更改记录中其他字段的值，datetime 字段的值都不会改变，除非你手动更改它。

timestamp

1. 允许为空值，不可以自定义值，为空值时没有任何意义
2. 默认值为 CURRENT_TIMESTAMP()，其实也就是当前的系统时间
3. timestamp 列不可以用程序设置值，只能由数据库自动去修改(手动修改也可以)，所以在插入记录时不需要指定 timestamp 字段的名称和 timestamp 字段的值，你只需要在设计表的时候添加一个 timestamp 字段即可，插入后该字段的值会自动变为当前系统时间
4. 以后任何时间修改表中的记录时，对应记录的 timestamp 值会自动被更新为当前的系统时间
5. TIMESTAMP列创建后的格式是
   date_change_last_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
   结论：timestamp 类型适合用来记录数据的最后修改时间，因为只要你更改记录中其他字段的值，timestamp字段的值都会被自动更新。

datetime对比timestamp

• 两者的存储方式不一样
  对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。而对于DATETIME，不做任何改变，基本上是原样输入和输出。
• 两者所能存储的时间范围不一样
  timestamp所能存储的时间范围为：’1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’。
  datetime所能存储的时间范围为：’1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’。

总结：timestamp有自动初始化和更新，当你update某条记录的时候，该列值会自动更新，这是和datetime最大的区别。每个表都应该有一列使用 timestamp 用于自动记录更新时间，如果需要记录创建时间，则使用 datetime。TIMESTAMP和DATETIME除了存储范围和存储方式不一样，没有太大区别。当然，对于跨时区的业务，TIMESTAMP更为合适。

自动初始化和更新
自动初始化指的是如果对该时间日期字段没有显性赋值，则自动设置为当前系统时间。
自动更新指的是如果修改其它字段，则该字段的值将自动更新为当前系统时间。
它与explicit_defaults_for_timestamp参数有关。
默认情况下，该参数的值为OFF：show variables like '%explicit_defaults_for_timestamp%';

参考：
MySQL5 日期类型 DATETIME 和 TIMESTAMP 相关问题详解

选择数据类型的原则

MySQL支持多种数据类型，选择合适的数据类型存储数据对MySQL存储引擎来说至关重要。

1. 选择最小数据类型
   通常情况下，选择可以正确存储数据的最小数据类型。因为最小数据类型占用的磁盘、内存和缓存更少，执行的更快。在选择合适最小数据类型的时候，选择你认为不会超出范围的最小类型。
2. 选择简单数据类型
   简单数据类型的各种操作通常需要更少的CPU周期。
3. 避免列值为NULL
   除非非常有必要，通常情况下，需要将列值设置为NOT NULL。NULL对索引、索引统计和存储来说是比较复杂的，NULL对查询是很难优化的。

语法

各种取数据库取前N条记录的SQL语句

MySQL：
select * from table1 limit 10; 或 select * from table1 limit 0,10;
SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last
初始记录行的偏移量是 0，指定第二个参数为 -1时表示查询从某一个偏移量到记录集的结束所有的记录行。
SQL Server：
select top (10) * from table1 where 1=1;
每页显示perpage条记录，按照id的排序，取第pageno页的记录：
select top perpage*pageno * from table where id not in(select top (pageno-1)*perpage id from table1);
oracle：
select * from table1 where rownum<=10;
oracle取第11-20条：
select * from (select *,rownum num from table1 where rownum<=20) where num>=10;
oracle按id排序后取11-20条
select * from (select *,rownum num from (select * from table1 tt order by id desc) where rownum<=20) where num>=10;
Access：
select top (10) * from table1;
db2：
select 列名 from table1 where 1=1 fetch first 10 rows only;

AND/OR语句执行优先级

MySQL中，AND的执行优先级高于OR。即，在没有小括号()的干预下，总是先执行AND语句，再执行OR语句。例：
select * from table where 条件1 AND 条件2 OR 条件3
等价于
select * from table where ( 条件1 AND 条件2 ) OR 条件3
select * from table where 条件1 AND 条件2 OR 条件3 AND 条件4
等价于
select * from table where ( 条件1 AND 条件2) OR ( 条件3 AND 条件4 )
其实and和or运算，就是逻辑运算中的 &(与) 和 |(或)运算。

数据表主键

主键只能有一个，所谓的多个是联合主键，即用多个字段一起作为一张表的主键。主键作用是保证数据的唯一性和完整性，同时通过主键检索表能够增加检索速度。
创建方式：

1. 客户端选择多列，设置为主键；
2. 创建语句指定：Primary Key (column1, column2)
3. 建表后更改：ALTER TABLE <name> WITH NOCHECK ADD CONSTRAINT [PK_<name>] PRIMARY KEY NONCLUSTERED ([column1], [column2])

插入语句的values&value

1. Mysql插入语句之value与values区别：
   在 MySQL 的插入 insert 子句中，value 和 values 没有什么区别，可以混用；通过不太严密的数据测试得出结论：使用 value 比 values 的执行效率要高，耗时少；在插入单行的时候使用VALUES，在插入多行的时候使用VALUE；
2. 文章：也是 value 效率要高的现象；values 是 MySQL 官方使用的关键字；
3. stackoverflow的总结：没区别，都是关键字。
4. 和数据库有关，SQL Server没有value关键字。

count(1)、count(*) and count(column)

三者都是用来统计行数（记录数）；
使用explain解释，count(1)和count(*)没有差别；两者都会使用索引。
count(1) 会统计表中的所有的记录数，包含字段为null 的记录。
count(字段) 会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null 的记录。
count(*)包括所有列，相当于行数，在统计结果时，不会忽略列值为NULL
count(1)包括忽略所有列，用1代表代码行，在统计结果时，不会忽略列值为NULL
count(列名)只包括列名那一列，在统计结果时，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。

执行效率上：

1. 列名为主键，count(列名)会比count(1)快
2. 列名不为主键，count(1)会比count(列名)快
3. 如果表多个列并且没有主键，则count(1)的执行效率优于count(*)
4. 如果有主键，则select count(主键)的执行效率是最优的
5. 如果表只有一个字段，则select count(*)最优

count(column)执行耗时最小，前两个几乎没有区别；

MySQL table schema操作

通常数据库CRUD操作，是针对数据库中的文件。MySQL数据库中还有一些表（是view，只能做select操作）记录现有表的meta data，比如某个column的名字和表的定义。

1. 列出test数据库中所有的表名，类型(普通表还是view)和使用的引擎
   select table_name, table_type, engine FROM information_schema.tables WHERE table_schema = 'test' ORDER BY table_name DESC;
2. 检查数据库test中的某一个表d_ad是否存在：
   select count(1) from information_schema.tables where table_schema = 'test' and table_name = 'd_ad';
3. 检查都一张表test.d_ad的某一栏ad_id的类型：
   select column_type from information_schema.columns where TABLE_SCHEMA = 'test' and TABLE_NAME = 'd_ad' and COLUMN_NAME = 'ad_id';
4. 更改某一栏的定义;
   alter table test.d_ad modify column ad_id bigint(20) DEFAULT 0;

系统函数

无论是MySQL还是SQL server都自带很多内置函数。善用或者知道这些自带函数，可以大大加大开发效率。

now()

DATE_SUB(NOW(), INTERVAL 10 MINUTE)

cast()

select cast(date as signed) as date from <table_name>;
CAST() 和CONVERT() 函数可用来获取一个类型的值，并产生另一个类型的值。
这个类型 可以是以下值其中的 一个：
BINARY[(N)]
CHAR[(N)]
DATE
DATETIME
DECIMAL
SIGNED [INTEGER]
TIME循环
UNSIGNED [INTEGER]

CONVERT()

find_in_set

<select id="selectNewCouponsByUid" resultMap="couponMap">
    select <include refid="Base_Column_List"/> from coupon
        where uid = #{uid} and now() between effective_from and effective_to for update
    union
    select <include refid="Base_Column_List"/> from coupon force index(idx_req_id)
        where req_id like concat('m1_', #{uid}, '%') and now() between effective_from and effective_to for update
</select>

报错

Specified key was too long; max key length is 1000/767 bytes;

在MySQL建表时报错如下：
Specified key was too long; max key length is 1000 bytes;
或
Specified key was too long; max key length is 767 bytes;
原因：索引长度超出限制，故创建失败。
具体说来：
在MyISAM表中，创建表（索引）时，索引长度不能超过1000bytes；
使用InnoDB 引擎，创建表（索引）时，长度不成超过767byts ;
计算规则：建立索引时，数据库计算key的长度是累加所有Index用到的字段的char长度后再按以下比例乘起来不能超过限定的key长度：
latin1 = 1 byte = 1 character
uft8 = 3 byte = 1 character
gbk = 2 byte = 1 character
解决方法：

1. 将有索引的列长度减小即可，推荐；下面的方法 2 & 3 可能需要 DBA 的支持，开发人员没有权限；
2. 让 MySQL 支持比较长的索引，然后在插入表时，添加 ROW_FORMAT=DYNAMIC ，自动格式化索引。查看数据库信息：

show variables like 'innodb_large_prefix';
show variables like 'innodb_file_format';

修改最大索引长度限制：

set global innodb_large_prefix=1; 
set global innodb_file_format=BARRACUDA;
set global innodb_file_format_max=BARRACUDA;

3. 修改建表 SQL 语句添加 ROW_FORMAT=DYNAMIC：

create table idx_length_test_02 (
  id int auto_increment primary key,
  name varchar(255)
) 
ROW_FORMAT=DYNAMIC default charset utf8mb4;

数据库管理/连接工具

DataGrip

JetBrain公司出品，操作界面、风格和快捷键和IDEA非常相似，有着IDEA一样的插件化思想。入手快。
参考：DataGrip使用经验之谈

DBeaver

DBeaver，基于Eclipse开发，开源免费，支持各大操作系统，操作界面与Eclipse非常类似。是一个通用的数据库管理工具和 SQL 客户端，支持各种常见的数据库，以及其他兼容 JDBC 的数据库，如一些不常见的数据库如惠普的vertica。DBeaver 提供一个图形界面用来查看数据库结构、执行SQL查询和脚本，浏览和导出数据，处理BLOB/CLOB 数据，修改数据库结构等。更新频繁，个人最爱。

建模工具 MySQL Workbench

MySQL Workbench是数据库架构师和开发人员的可视化数据库设计、管理的工具，它是著名的数据库设计工具DBDesigner4的继任者。你可以用MySQL Workbench设计和创建新的数据库图示，建立数据库文档。它同时有开源和商业化的两个版本。支持主流操作系统。

MAC上的MySQL管理工具 Sequel Pro

Sequel Pro 的原名是CocoaMySQL，是一个与phpMyAdmin相像的MySQL管理工具。它是由Cocoa和面对对象的C（Mac OSX）编写的。Sequel Pro允许你编辑数据库，表格（字段和索引）和列，执行个性化查找和导入导出数据。
缺点：不适用于其他系统。
参考：Mac 平台 MySQL连接工具 Sequel Pro 使用经验之谈

Oracle SQL Developer

Oracle SQL Developer 是一个免费非开源的用以开发数据库应用程序的图形化工具，使用 SQL Developer 可以浏览数据库对象、运行 SQL 语句和脚本、编辑和调试 PL/SQL 语句。另外还可以创建执行和保存报表。该工具可以连接任何 Oracle 9.2.0.1 或者以上版本的 Oracle 数据库，支持主流操作系统。

Navicat Lite

开源免费。快速、可靠并价格相宜的资料库管理工具，大可使用来简化资料库的管理及降低系统管理成本。Navicat是以直觉化的使用者图形介面所而建的，让你可以以安全且简单的方式建立、组织、存取并共用资讯。Navicat 支持常见的各种数据库。Navicat 提供商业版 Navicat Premium 和 免费的版本 Navicat Lite 。但目前 Navicat 已不再提供 LITE 版本。
缺点：免费版本已停止更新。

监控工具

其他工具

表结构自动同步工具——MySQL-Schema-Sync

官网：https://github.com/hidu/mysql-schema-sync
Go开发的跨平台的 MySQL 表结构自动同步工具。主要用于解决多个环境数据库表结构不同步问题。支持功能：

• 同步新表
• 同步字段 变动：新增、修改
• 同步索引 变动：新增、修改
• 支持预览（只对比不同步变动）
• 邮件通知变动结果
• 支持屏蔽更新表、字段、索引、外键
• 支持本地比线上额外多一些表、字段、索引、外键