MySQL学习笔记

MySQL学习笔记

整理：Jims of 肥肥世家

Copyright ? 2004 本文遵从GNU 的自由文档许可证(Free Document License)的条款，欢迎转载、修改、散布。 发布时间: 2004年11月06日 最近更新: 2005年01月12日

Chapter 1. MySQL数据库的数据

MySQL数据库是由数据组成的，为了能方便管理和使用这些数据，我们把这些数据进行分类，形成各种数据类型，有数据值的类型，有表中数据列的类型，有数据表的类型。理解MySQL的这些数据类型能使我们更好地使用MySQL数据库。下面对各种数据类型进行简单的介绍。

1.1. 数据值类型(data type)

对MySQL中数据值的分类，有数值型、字符型、日期型和空值等，这和一般的编程语言的分类差不多。

1.1.1. 数值

MySQL中的数值分整型和浮点型两种。MySQL支持科学记数法。整型可以是十进制，也可是十六进制数。

1.1.2. 字符串

MySQL支持以单或双引号包围的字符序列。如“MySQL tutorial”、?Mysql Database?。 MySQL能识别字符串中的转义序列，转义序列用反斜杠(\\)表示。下面是一个转义序列列表。

Table 1.1. 转义序列 转义序列 \\0 含义 NUL(ASCII的0值) \\' \\\\\b \\n \\r \\t \\\\ \\Z 单引号 双引号 后退符 换行符 回车符 制表符 反斜杠 Ctrl+Z 如果字符串本身包含有单双引号，则用以下三种方法中的一种来表示： 字符串的引号和字符串两端的引号双同，则双写该引号。如：'mysql''s test'。 用与字符串的引号不同的引号把字符串引起来，如：\。

用反斜杠转义引号，如：\test\，'mysql\\' test'。这样就不用理会字符串两端的是单引号还是双引号了。

字符串可由一个十六进制数表示，如0x61表示字符\。由MySQL 4.0开始，字符串值也可用ANSI SQL表示法X'val'来表示。如X'61'表示字符\。 从MySQL 4.1开始，可以为字符串值专门指定一个字符集。

1.1.3. 日期和时间

MySQL默认按“年-月-日”的顺序显示日期。

1.2. 列类型(column type)

MySQL数据库的表是一个二维表，由一个或多个数据列构成。每个数据列都有它的特定类型，该类型决定了MySQL如何看待该列数据，我们可以把整型数值存放到字符类型的列中，MySQL则会把它看成字符串来处理。MySQL中的列类型有三种：数值类、字符串类和日期/时间类。从大类来看列类型和数值类型一样，都是只有三种。但每种列类型都还可细分。下面对各种列类型进行详细介绍。

1.2.1. 数值类的数据列类型

数值型的列类型包括整型和浮点型两大类。

Table 1.2. 数值类数据列类型

数据列类型 TINYINT SMALLINT MEDIUMINT INT BIGINT FLOAT DOUBLE DECIMAL 存储空间 1字节 2字节 3字节 4字节 8字节 4字节 8字节 M+2字节 描述 非常小的正整数，带符号：-128~127，不带符号：0~255 小整数，带符号：-32768~32767，不带符号：0~65535 中等大小的整数，带符号：-8388608~8388607，不带符号：0~16777215 标准整数，带符号：-2147483648~2147483647，不带符号：0~4294967295 大整数，带符号：-9223372036854775808~9233372036854775807，不带符号：0~18446744073709551615 单精度浮点数，最小非零值：+-1.175494351E-38，最大非零值：+-3.402823466E+38 双精度浮点数，最小非零值：+-2.2250738585072014E-308，最大非零值：+-1.7976931348623157E+308 以字符串形式表示的浮点数，它的取值范围可变，由M和D的值决定。 1.2.1.1. 整型数据列类型

MySQL有五种整型数据列类型，即TINYINT，SMALLINT，MEDIUMINT，INT和BIGINT。它们之间的区别是取值范围不同，存储空间也各不相同。在整型数据列后加上UNSIGNED

属性可以禁止负数，取值从0开始。

声明整型数据列时，我们可以为它指定个显示宽度M(1~255)，如INT(5)，指定显示宽度为5个字符,如果没有给它指定显示宽度，MySQL会为它指定一个默认值。显示宽度只用于显示，并不能限制取值范围和占用空间，如：INT(3)会占用4个字节的存储空间，并且允许的最大值也不会是999,而是INT整型所允许的最大值。 1.2.1.2. 浮点型数据列类型

MySQL有三种浮点型数据列类型，分别是：FLOAT，DOUBLE和DECIMAL。浮点类数据类型有一个最大可表示值和一个最小非零可表示值，最小非零可表示值决定了该类型的精确度。

MySQL 4.0.2版之后，FLOAT和DOUBLE都可以指定UNSIGNED属性。当指定该属性时，取值范围不平移到正数区间，而只是简单地把浮点类型的负数部份去掉。

浮点类型也有M(1~255)和D(1~30，且不能大于M-2)。分别表示显示宽度和小数位数。M和D在FLOAT和DOUBLE中是可选的，默认，当MySQL版本大于3.23.6时，FLOAT和DOUBLE类型将被保存为硬件所支持的最大精度。DECIMAL的M和D值在MySQL3.23.6后可选，默认D值为0,M值为10。

1.2.1.3. 如何选择数值类数据列类型？

为了节省存储空间和提高数据库处理效率，我们应根据应用数据的取值范围来选择一个最适合的数据列类型。如果把一个超出数据列取值范围的数存入该列，则MySQL就会截短该值，如：我们把99999存入SMALLINT(3)数据列里，因为SMALLINT(3)的取值范围是-32768~32767，所以就会被截短成32767存储。显示宽度3不会影响数值的存储。只影响显示。 对于浮点数据列，存入的数值会被该列定义的小数位进行四舍五入。如把一个1.234存入FLOAT(6.1)数据列中，结果是1.2。

DECIMAL与FLOAT和DOUBLE的区别是：DECIMAL类型的值是以字符串的形式被储存起来的，它的小数位数是固定的。它的优点是，不会象FLOAT和DOUBLE类型数据列那样进行四舍五入而产生误差，所以很适合用于财务计算；而它的缺点是：由于它的存储格式不同，CPU不能对它进行直接运算，从而影响运算效率。DECIMAL(M，D)总共要占用M+2个字节。

1.2.1.4. 数值类数据列的属性

ZEROFILL属性适用于所有数值类数据列类型，作用是，如果数值的宽度小于定义的显示宽度，则在数值前填充0。

UNSIGNED属性不允许数据列出现负数。

AUTO_INCREMENT属性可生成独一无二的数字序列。只对整数类的数据列有效。 NULL和NOT NULL属性设置数据列是否可为空。 DEFAULT属性可为数据列指定默认值。

1.2.2. 字符串类数据列类型

字符串可以用来表示任何一种值，所以它是最基本的类型之一。我们可以用字符串类型来存储图象或声音之类的二进制数据，也可存储用gzip压缩的数据。下表介绍了各种字符串类型：

Table 1.3. 字符串类数据列类型

类型 CHAR[(M)] VARCHAR[(M)] TINYBLOD，TINYTEXT BLOB，TEXT 最大长度 M字节 M字节 2^8-1字节 2^16-1字节 占用存储空间 M字节 L+1字节 L+1字节 L+2 MEDIUMBLOB，MEDIUMTEXT LONGBLOB，LONGTEXT ENUM('value1','value2',...) SET('value1','value2',...) 2^24-1字节 2^32-1字节 65535个成员 64个成员 L+3 L+4 1或2字节 1,2,3,4或8字节 L+1、L+2是表示数据列是可变长度的，它占用的空间会根据数据行的增减面则改变。数据行的总长度取决于存放在这些数据列里的数据值的长度。L+1或L+2里多出来的字节是用来保存数据值的长度的。在对长度可变的数据进行处理时，MySQL要把数据内容和数据长度都保存起来。

如果把超出字符串最大长度的数据放到字符类数据列中，MySQL会自动进行截短处理。 ENUM和SET类型的数据列定义里有一个列表，列表里的元素就是该数据列的合法取值。如果试图把一个没有在列表里的值放到数据列里，它会被转换为空字符串(“”)。

字符串类型的值被保存为一组连续的字节序列，并会根据它们容纳的是二进制字符串还是非二进制字符而被区别对待为字节或者字符：

二进制字符串被视为一个连续的字节序列，与字符集无关。MySQL把BLOB数据列和带BINARY属性的CHAR和VARCHAR数据列里的数据当作二进制值。

非二进制字符串被视为一个连续排列的字符序列。与字符集有关。MySQL把TEXT列与不带BINARY属性的CHAR和VARCHAR数据列里的数据当作二进制值对待。

在MySQL4.1以后的版本中，不同的数据列可以使用不同的字符集。在MySQL4.1版本以前，MySQL用服务器的字符集作为默认字符集。

非二进制字符串，即我们通常所说的字符串，是按字符在字符集中先后次序进行比较和排序的。而二进制字符串因为与字符集无关，所以不以字符顺序排序，而是以字节的二进制值作为比较和排序的依据。下面介绍两种字符串的比较方式：

二进制字符串的比较方式是一个字节一个字节进行的，比较的依据是两个字节的二进制值。也就是说它是区分大小写的，因为同一个字母的大小写的数值编码是不一样的。

非二进制字符串的比较方式是一个字符一个字符进行的，比较的依据是两个字符在字符集中的先后顺序。在大多数字符集中，同一个字母的大小写往往有着相同的先后顺序，所以它不区分大小写。

二进制字符串与字符集无关，所以无论按字符计算还是按字节计算，二进制字符串的长度都是一样的。所以VARCHAR(20)并不表示它最多能容纳20个字符，而是表示它最多只能容纳可以用20个字节表示出来的字符。对于单字节字符集，每个字符只占用一个字节，所以这两者的长度是一样的，但对于多字节字符集，它能容纳的字符个数肯定少于20个。 1.2.2.1. CHAR和VARCHAR

LOCAL的能力，客户端默认也是禁止使用的，但可用mysql程序的--local-infile选项启用它。 国际化和本地化，国际化是指软件能够在世界多个国家地区使用，而本地化则是指可从国际化软件中选择一套适合本地区的语言和习惯的设置来使用。在MySQL中的国际化和本地化设置有以下几方面内容：

时区，如果时区设置不对，则服务器显示的时间将会和当地时间有冲突。设置方法可通过mysqld_safe脚本的--timezone选项来设置，但最好还是在选项文件里设置，如: [mysqld_safe] timezone=US/Central

配置显示信息的语言，MySQL能用多种语言来显示诊断信息与出错信息，默认是英语。查看share/mysql目录下有几个以语言名称作为目录名的目录就可知道有哪些语言可供选择。可用--language启动选项来指定语言，如--language=/usr/local/mysql/share/mysql/french。 配置服务器的字符集，MySQL支持多种字符集，可在share/mysql/charsets目录下查询支持的字符集，也可用show variables like 'character_sets'来显示支持的字符集清单。MySQL把latin1作为默认的字符集。可在编译时用--with-charset指定另外一个字符集为默认字符集。如要增加另外的字符集支持，可用--with-extra-charasets选项进行添加。如： % ./configure --with-extra-charsets=latin1,gb2312,big5

--with-extra-charsets有两个特殊的选项，一个是all，代表所有可用字符集；一个是complex，代表所有的复杂字符集(包括多字节字符集和有特殊排序规则的字符集)。

服务器启动时，使用默认字符集，如需指定另外的字符集，需用--default-character-set选项指明。

在MySQL 4.1以前，如果在创建好数据表后改变服务器的默认字符集，就需对索引重新排序才能保证索引键值能够正确反映出数据表记录在新字符集下的排列顺序。重新排序的操作命令如下：

% myisamchk --recover --quick --set-character-set=gb2312 #在执行该语句需关闭服务器，适用于MyISAM数据表 也可用：

% mysqlcheck --repair --quick #不需关闭服务器，适用于各种数据表

或者用：

mysql> REPLACE TABLE ... QUICK；

在客户端，可用--default-character-set选项指定客户程序使用的字符集。--character-sets-dir选项可指出字符集文件的安装目录。

升级数据表到4.1，支持多字符集数据表。步骤如下： 用mysqldump程序备份数据库：

% mysqldump -p -u root --all-databases --opt > dumpfile.sql --all-databases选项的作用是转储所有数据库； --opt选项的作用是对转储文件进行优化。

关闭服务器，升级MySQL服务器软件到4.1版。 用备份文件重新加载数据表： % mysql -p -u root < dumpfile.sql

这样，字符集信息就被分配到每一个数据列中，此后，即使服务器改变了默认的字符集，各数据列的字符集也不会改变。当以后修改某个数据列的字符集时，服务器会自动重索引，以反映最新变化。

配置InnoDB表空间。InnoDB表空间在逻辑上是一个连接的存储区域，但实际上是由一个或多个磁盘文件组成。这些文件可以是普通的文件，也可以是一个未格式化的原始硬盘分区。InnoDB表空间通过一系列的配置选项来设置，其中最重要的有以下两个：

为确保服务器每次启动时都能调用同样的选项，InnoDB的选项最好存放到选文件中。下面是一个例子：

innodb_data_home_dir =

innodb_data_file_path=/usr/loca/mysql/data/idbdata1:10M:autoextend:max:100M 说明：

InnoDB表空间文件默认存放到了MySQL的数据目录中，名字叫idbdata1； 文件长度为10M；

可自动扩展，以8M为步长扩展，如有多个数据文件，只允许最后一个文件可自动扩展； 规定了最大的可扩展尺寸为100M。

innodb_data_home_dir，设置InnoDB表空间各组成文件的父目录，如果没有指出，则默认是MySQL的数据目录。

innodb_data_file_path，描述InnoDB主目录中各有关文件，包括文件名，文件长度和一些选项。各文件以分号分隔，各组成文件长度至少为10M。

把选项写入选项文件后，启动服务器就可自动创建和初始化InnoDB表空间。

利用原始磁盘分区作为InnoDB表空间可创建一个非常大的表空间，不受操作系统单文件最大容量的限制。并且能有效减少磁盘碎片的产生。要使用原始磁盘分区，需作如下配置： 首先，要进行初始化，在选项文件的[mysqld]中配置： innodb_data_home_dir=

innodb_data_file_path=/dev/hda1:10Gnewraw #初始化/dev/hda1这个10G容量的分区

启动服务器，服务器会对这个10G的分区进行初始化。 接着，关闭服务器，修改配置文件，把newraw改为raw，如： innodb_data_home_dir=

innodb_data_file_path=/dev/hda1:10Graw

重新启动服务器，MySQL就会以读/写方式使用该表空间了。在windows平台上配置InnoDB表空间时，windows路径名中的反斜杠可以写成单个的斜线字符(/)。也可写成两个反斜杠(\\\\)。如：

innodb_data_home_dir=

innodb_data_file_path=c:/mysql/data/ibdata1:10M;d:/ibdata2:20M

默认情况下，InnoDB的日志文件会存储在MySQL的数据目录，文件名以ib开头。一旦完成InnoDB表空间的初始化，就不能改变组成文件的大小，但可通过添加数据文件或设置自动扩展来增加表空间容量。如需通过增加文件的方法扩大表空间的容量，可按以下步骤进行： 关闭正在运行的MySQL服务器

如果InnoDB表空间的最后一个组成文件是可自扩展的，就要先把它改变成一个固定长度文件才能把另一个文件添加到它后面。方法是先计算出该文件的近似大小，重新设置，如： innodb_data_file_path=ibdata1:100M:autoextend 改成：

innodb_data_file_path=ibdata1:150M

把新的组成文件添加到文件清单的末尾，该文件可以是普通文件，也可以是一个原始硬盘分区。

重启服务器。

还有一种方法重新配置InnoDB表空间，就是先备份，再重新配置，最后重新加载备份。具体步骤如下：

使用mysqldump备份整个InnoDB数据库；

关闭服务器，删除所有InnoDB表空间、InnoDB日志文件 及InnoDB数据表的.frm文件； 重新配置InnoDB表空间；

配置完成后，用备份文件重载数据，生成新的InnoDB数据表。

4.7. 优化服务器

MySQL服务器为我们提供了丰富的参数，以调整服务器满足不同环境的要求。下面分别讨论一下这些参数：

服务器参数变量的设置。MySQL服务器参数可在服务器启动时设置，在MySQL4.0.3及以后的版本中，有些参数也允许在线设置。在MySQL4.0.2及以后的版本里，可以把一个变量名视为一个选项名来设置。如数据表缓冲区的尺寸由服务器参数talbe_cache来设置。如果需把它设置为128,则可以在命令行里增加

--table_cache=128

也可在选项文件中设置： [mysqld]

table_cache=128

在命令行选项中'_'可写'-'，变成：

--table-cache=128 #这种写法更像一个标准选项

还有一种是使用--set-variable或-O选项，如： --set-variable=table_cache=128 or

-O table_cache=128 在选项文件中可写成： [mysqld]

set-variable=table_cache=128

服务器参数分为全局级和会话级两个级别。全局级参数将影响整个服务器，会话级参数则只影响某给定客户连接上的工作。如果某个变量同时存在于两个级别，则服务器在客户建立连接时用全局变量的值去初始化相应的会话级参数，一旦客户连接建立起来后，对全局参数所作的修改不会影响到相应的会话级参数当前值。设置全局参数和会话级参数的语句： 全局级：

mysql> SET GLOBAL variable = value; mysql> SET @@GLOBAL.variable = value; 会话级：

本文来源：https://www.bwwdw.com/article/29yv.html