MySQL

MySQL 是最流行的关系型数据库管理系统,在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System:关系数据库管理系统)应用软件之一。 数据库(Database)是按照数据结构来组织、存储和管理数据的仓库。 每个数据库都有一个或多个不同的 API 用于创建,访问,管理,搜索和复制所保存的数据。 我们也可以将数据存储在文件中,但是在文件中读写数据速度相对较慢。 所以,现在我们使用关系型数据库管理系统(RDBMS)来存储和管理大数据量。所谓的关系型数据库,是建立在关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据。 RDBMS 即关系数据库管理系统(Relational Database Management System)的特点: 1.数据以表格的形式出现 2.每行为各种记录名称 3.每列为记录名称所对应的数据域 4.许多的行和列组成一张表单 5.若干的表单组成database 在我们开始学习MySQL 数据库前,让我们先了解下RDBMS的一些术语: 数据库: 数据库是一些关联表的集合。 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。 列: 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。 行:一行(=元组,或记录)是一组相关的数据,例如一条用户订阅的数据。 冗余:存储两倍数据,冗余降低了性能,但提高了数据的安全性。 主键:主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。 外键:外键用于关联两个表。 复合键:复合键(组合键)将多个列作为一个索引键,一般用于复合索引。 索引:使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件,目的是保证数据的一致性。 MySQL 为关系型数据库(Relational Database Management System), 这种所谓的"关系型"可以理解为"表格"的概念, 一个关系型数据库由一个或数个表格组成

MySQL基础

MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统,关联数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。 MySQL 是开源的,目前隶属于 Oracle 旗下产品。 MySQL 支持大型的数据库。可以处理拥有上千万条记录的大型数据库。 MySQL 使用标准的 SQL 数据语言形式。 MySQL 可以运行于多个系统上,并且支持多种语言。这些编程语言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。 MySQL 对PHP有很好的支持,PHP 是目前最流行的 Web 开发语言。 MySQL 支持大型数据库,支持 5000 万条记录的数据仓库,32 位系统表文件最大可支持 4GB,64 位系统支持最大的表文件为8TB。 MySQL 是可以定制的,采用了 GPL 协议,你可以修改源码来开发自己的 MySQL 系统。

MySQL拓展

MySQL 存储引擎 存储引擎概述 数据库最核心的一点就是用来存储数据,数据存储就避免不了和磁盘打交道。那么数据以哪种方式进行存储,如何存储是存储的关键所在。所以存储引擎就相当于是数据存储的发动机,来驱动数据在磁盘层面进行存储。 1、数据库: 物理操作系统文件或其他形式文件类型的集合。 在MySQL数据库中, 数据库文件可以是frm、 MYD、 MYI、 ibd结尾的文件。 当使用NDB引擎时, 数据库的文件可能不是操作系统上的文件, 而是存放于内存之中的文件, 但是定义仍然不变。 2、实例: MySQL数据库由后台线程以及一个共享内存区组成。 共享内存可以被运行的后台线程所共享。 需要牢记的是, 数据库实例才是真正用于操作数据库文件的。 这两个词有时可以互换使用, 不过两者的概念完全不同。 在MySQL数据库中, 实例与数据库的关系通常是一一对应的, 即一个实例对应一个数据库, 一个数据库对应一个实例。 但是, 在集群情况下可能存在一个数据库被多个数据实例使用的情况。 MySQL被设计为一个单进程多线程架构的数据库, 这点与SQL Server比较类似, 但与Oracle多进程的架构有所不同( Oracle的Windows版本也是单进程多线程架构的) 。 这也就是说, MySQL数据库实例在系统上的表现就是一个进程。 3、mysql配置文件 当启动实例时, MySQL数据库会去读取配置文件, 根据配置文件的参数来启动数据库实例。这与Oracle的参数文件( spfile) 相似, 不同的是, Oracle中如果没有参数文件, 在启动实例时会提示找不到该参数文件, 数据库启动失败。 而在MySQL数据库中, 可以没有配置文件, 在这种情况下, MySQL会按照编译时的默认参数设置启动实例。 用以下命令可以查看当MySQL数据库实例启动时, 会在哪些位置查找配置文件。 MySQL数据库是按/etc/my.cnf→/etc/mysql/my.cnf→/usr/local/mysql/etc →~/.my.cnf的顺序读取配置文件的。 可能如果几个配置文件中都有同一个参数,MySQL数据库以哪个配置文件为准?MySQL数据库会以读取到的最后一个配置文件中的参数为准。 在Linux环境下, 配置文件一般放在/etc/my.cnf下。 在Windows平台下, 配置文件的后缀名可能是.cnf, 也可能是.ini。 从概念上来说, 数据库是文件的集合,是依照某种数据模型组织起来并存放于二级存储器中的数据集合; 数据库实例是程序, 是位于用户与操作系统之间的一层数据管理软件, 用户对数据库数据的任何操作, 包括数据库定义、 数据查询、 数据维护、 数据库运行控制等都是在数据库实例下进行的, 应用程序只有通过数据库实例才能和数据库打交道。 更为直白的方式来解释: 数据库是由一个个文件组成( 一般来说都是二进制的文件) 的,要对这些文件执行诸如SELECT、 INSERT、UPDATE和DELETE之类的数据库操作是不能通过简单的操作文件来更改数据库的内容, 需要通过数据库实例来完成对数据库的操作。

MySQL拓展

mysql中utf8和utf8mb4区别

mysql中utf8和utf8mb4区别

一、简介

MySQL在5.5.3之后增加了这个utf8mb4的编码,mb4就是most bytes 4的意思,专门用来兼容四字节的unicode。好在utf8mb4是utf8的超集,除了将编码改为utf8mb4外不需要做其他转换。当然,为了节省空间,一般情况下使用utf8也就够了。

二、内容描述

那上面说了既然utf8能够存下大部分中文汉字,那为什么还要使用utf8mb4呢? 原来mysql支持的 utf8 编码最大字符长度为 3 字节,如果遇到 4 字节的宽字符就会插入异常了。三个字节的 UTF-8 最大能编码的 Unicode 字符是 0xffff,也就是 Unicode 中的基本多文种平面(BMP)。也就是说,任何不在基本多文本平面的 Unicode字符,都无法使用 Mysql 的 utf8 字符集存储。包括 Emoji 表情(Emoji 是一种特殊的 Unicode 编码,常见于 ios 和 android 手机上),和很多不常用的汉字,以及任何新增的 Unicode 字符等等。

三、问题根源

最初的 UTF-8 格式使用一至六个字节,最大能编码 31 位字符。最新的 UTF-8 规范只使用一到四个字节,最大能编码21位,正好能够表示所有的 17个 Unicode 平面。

utf8 是 Mysql 中的一种字符集,只支持最长三个字节的 UTF-8字符,也就是 Unicode 中的基本多文本平面。

Mysql 中的 utf8 为什么只支持持最长三个字节的 UTF-8字符呢?我想了一下,可能是因为 Mysql 刚开始开发那会,Unicode 还没有辅助平面这一说呢。那时候,Unicode 委员会还做着 “65535 个字符足够全世界用了”的美梦。Mysql 中的字符串长度算的是字符数而非字节数,对于 CHAR 数据类型来说,需要为字符串保留足够的长。当使用 utf8 字符集时,需要保留的长度就是 utf8 最长字符长度乘以字符串长度,所以这里理所当然的限制了 utf8 最大长度为 3,比如 CHAR(100) Mysql 会保留 300字节长度。至于后续的版本为什么不对 4 字节长度的 UTF-8 字符提供支持,我想一个是为了向后兼容性的考虑,还有就是基本多文种平面之外的字符确实很少用到。

要在 Mysql 中保存 4 字节长度的 UTF-8 字符,需要使用 utf8mb4 字符集,但只有 5.5.3 版本以后的才支持(查看版本: select version();)。我觉得,为了获取更好的兼容性,应该总是使用 utf8mb4 而非 utf8. 对于 CHAR 类型数据,utf8mb4 会多消耗一些空间,根据 Mysql 官方建议,使用 VARCHAR 替代 CHAR。

四、解决方案

升级 mysql 版本,并将utf8字符集升级到utf8mb4

升级你的 mysql 到 5.5.3 之后即可,查看当前环境版本:

select version();

MySQL在5.5.3之后增加了这个utf8mb4的编码,mb4就是most bytes 4的意思,专门用来兼容四字节的unicode。好在utf8mb4是utf8的超集,除了将编码改为utf8bp4外不需要做其他转换。当然,为了节省空间,一般情况下使用utf8也就够了。

所以好的技术就是,采用对当前而言最好的解决方案,然后再逐步迭代满足新的需求。

直接修改表结构

-- 方法一,如果遇到某个列字符集转换完后字节数超限了,会提示错误
--1、修改数据库字符集,或修改表默认字符集 alter table j1 default character set utf8mb4;
ALTER DATABASE test CHARACTER SET = utf8mb4;
--2、随后再修改所有字符型列的字符集 alter table j1 modify name varchar(20) character set utf8mb4 not null default '';
ALTER TABLE `test` CHANGE COLUMN `name` `name` varchar(12) CHARACTER SET utf8mb4;

-- 方法二,如果遇到某个列字符集转换完后字节数超限了,则会将这个列数据类型转换成可以容纳更大长度的类型,比如从 TEXT 转成 LONGTEXT 等。
--直接转换表字符集
alter table test convert to character set utf8mb4;

--方法三
--如果不放心,可以用mysqldump逻辑备份方式,用utf8mb4字符集把数据备份出来,新建表,恢复回去,应该也可以的。

修改数据库默认配置

[client]
default-character-set = utf8mb4
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
[mysql]
default-character-set = utf8mb4

如果你使用的是java语言,需要将jdbc驱动包升级到 mysql-connector-java-5.1.14.jar。

强行过滤掉生僻字符串

从业务和技术的角度综合考虑,可以做个折中,将生僻字符串提前过滤掉,因为这类字符串本来就使用的很少,即使存进数据库了,展示、查询的时候也会多少有其它的问题,不如直接过滤掉,mysql 不支持四字节的 utf8 一方面可能是历史包袱,另一方面估计也是为了省空间。

shell 过滤

可以直接先用 sed、awk、python、perl 处理下要 load 入库的脚本,将四字节的生僻字全过滤再入库:

java 中的过滤操作

判断MySql支持Unicode字符的方法,伪码为:

for i=1->n
int c=str.codePointAt(i);
if (c<0x0000||c>0xffff) {
    return false;
}

避开客户端乱码:二进制存储与查询

为避免web页面或者终端本身不支持utf8四字节,可以采用二进制的方式来操作

create table t1(name varchar(20) charset utf8mb4);
insert into t1 values(0xF0A09080);

set charset binary;
select * from t1;

应用、系统对 utf-8 四字节字符的支持

最后顺便总结下4字节utf8字符的系统支持情况:

许多的数据库软件或者shell终端都不支持4字节utf8字符, 比如 Navicat、SecureCRT

以 php 场景为例说明:

php连接会话设置编码utf8, mysql后端字段为text character set utf8: 写入内容从4字节utf8字符处被截断

php连接会话设置编码utf8mb4, mysql后端字段为text character set utf8: 内容可以完整写入, 但是4字节utf8字符被替换为问号"?"

php连接会话设置编码utf8mb4, mysql后端字段为text character set utf8mb4: 完整支持4字节utf8字符

从平台支持上来看, 随着winxp的逐步淘汰, 对4字节utf8字符的支持还是有必要的. 官方手册对utf8mb4字符的说明中指出, utf8mb4是utf8的超集, 因此可放心升级.