MySQL权威指南读书笔记(三)

第二章：MYSQL数据库里面的数据

    用想用好MYSQL，就必须透彻理解MYSQL是如何看待和处理数据的。本章主要讨论了两个问题：一是ＳＱＬ所能处理的数据值的类型；二是这些数据类型在实际应用中需要注意的问题。

    首先我们看看mysql能够支持的数据类型，和其它的数据库一样，我们可以处理各种数值（整型，浮点），字符串型，日期／时间型，ＮＵＬＬ值等等。大家在使用的需要注意不同类型的数值的格式是不一样的。在这里，对字符串的处理有一个比较特殊的地方大家需要理解。因为字符串两端是需要用引号（单引号，双引号）括起来的，但是如果字符串本身里面也包括了引号（单引号，双引号），我们应该怎么办呢。这时就必须用以下三种方法之一来标识这个特殊的字符串。

    １， 如果字符串内部的引号字符与字符串两端的引号字符相同，则双写该引号

‘I can’’t’
“He said,””I told you so, “”’

    ２， 用与字符串内部的引号字符不同的引号把该字符串引起来，此时，就不用双写字符串内部的引号了

“I can’t”
‘He said,”I told you so,”’

    ３， 用反斜杠对字符串内部的引号字符进行转义，此时与字符串两端的引号无关

‘I can\’t’
“He said,\”I told you so,\””

    下面我们看数据列类型，真的很奇怪，数据列里面放数据，两者类型不相同。事实上，把数据列类型叫做列类型也许更合适一些，数据类型只是一种含义广泛的分类方法，比如：数值，字符串等等。而列类型是对一个给定的数据列里面的值有哪些具体特点的准确描述。比如smallint或varchar(10)等。也就是说，数据列类型决定了mysql如何对待这些数据，你不能把abc放到一个数值列里面！如果你给你的数据找到一个合适的位置，下列问题时必须考虑的：

    需要把哪些种类的数值保存到数据表里面？
    这种类型的值要占用多少存储空间？
    这种类型的值长度是否固定不变？
    这种类型的值如何进行比较和排序？
    这种类型的值可以用null值吗？
    如何对这种类型的值进行索引呢？

    当你对这些问题都能心中有数的时候，就可以选择具体的列类型了，当然，每一种列类型都有相应的特征表格大家可以查询。需要注意的是这几点：

    1,显示宽度

    整数列的显示宽度与mysql需要用多少个字符来显示该列数值，与该整数需要的存储空间的大小都没有关系，比如，不管设定了显示宽度是多少个字符，bigint都要占用8个字节。

    2,截短处理

    数值类型列的取值范围是由它的具体类型决定的。如果想把一个超出列取值范围的值插入到各个列中，mysql会截短这个值，会先把这个值替换为该数据列取值范围的上限或下限，然后插入，这种截短仅与这个列的取值范围有关，与该列的显示宽度无关。比如说：把99999插入到一个类型为smallint(3)的数据列里面，就会被截短为32767

    3,前导0的问题

    如果想让某列里面的值都能全部整齐的按所定义的显示宽度显示出来，就需要使用zerofill属性，就可以在数值前面加入不定数目的0来显示数值，这个功能有些情况下很有用。但有一点需要注意，如果一个数值长度大于定义的显示宽度，这个数值也会正常显示，此时不受显示宽度的限制。

    4,enum和set

    需要记住，这两种数据列的列定义里面都要有一个列表，列表里面的元素就是该列的合法取值。如果你试图把一个不在这个列表里面数据放入本列，它就会被转换为空字符串！

    5,日期和时间

    在许多情况下，希望能够在数据列里面创建一条记录的时候可以记录当时的日期和时间。还希望这个时间值不会因为以后的操作而改变，但令人遗憾的是，目前仍然没有一种mysql列类型能够直接满足这个需要，现在我们一般用两个办法来解决这个问题：

    1,使用一个timestamp数据列,在需要创建一条新纪录的时候,把这个timestamp数据列设置为null.也就是把它初始化为当前的日期和实践.
    Insert into table_name (ts_col,…) values (null,…);
    在以后对这条纪录进行修改的时候,要用这个列里面的现有数据对它进行赋值.这种明确赋值的做法将抑制住mysql 的时间戳机制,让timestamp数据列里面的值不会自动改变.
    Update table_name set ts_col=ts_col where …

    2,使用一个datetime数据列,在需要创建一条新纪录的时候,用now() 函数来初始化这个数据列:
    insert into table_name (dt_col,…) values (now(),…);
    以后对这条纪录进行修改的时候,不要碰这个数据列.
    Update table_name set … anything but dt_col where …

    6,关于年份的转换问题

    如果你把一个两位数的年份值插入到一个year数据列里面, mysql 会自动地按照一下规则来进行转换:
    年份值 00-69 转换为 2000-2069
    年份值 70-99 转换为 1970-1999
    需要注意的是00这个年份,如果你执行:
    insert into table_name values (00)  其结果就会变成 0000
    因此,如果想使用一个没有世纪部分的值,结果得到2000年,就必须是用字符串格式 ’00’ 如果想让mysql 把一个向year 数据列插入的值看作字符串而不是数值,就应该用 concat()函数把这个值转化为字符串,无论输出参数是一个字符串还是一个数值, concat() 都会返回一个字符串.

    7,序列和编号

    作者之所以把序列和编号的问题单独拿出来讲解,是因为许多应用程序都需要一个独一无二的编号来把一条记录和其他的分别开来.

    在mysql 里面,这种独一无二的编号是通过数据列的auto_increment 属性而自动生成一组序列编号的办法来实现的.需要我们首先知道的是, mysql目前支持多种数据表类型.不同的数据表类型对于auto_increment 属性的处理机制是不一样的!所以我们不仅需要掌握有关auto_increment 属性的基本知识,还需要熟悉这种机制在各种的数据表类型中的差异.

    Mysql 3.23以前的版本仅仅支持比较基础的ISAM 数据表类型,在后来的版本中,其他几种数据表类型也逐步加了进来.包括 MyISAM 和 HEAP 类型等等.现在我们使用mysql的时候默认建立的数据表都是比较先进的MyISAM类型.我们先看比较基础的IASM表类型是如何看待数据列的auto_increment 属性.?

    A 如果试图把一个null 值插入到一个auto_increment 数据列里面,Mysql会自动生成一个序列编号并插入,
一般从1开始,依次递增
    B 不要把0插入auto_increment 数据列里面,我们不推荐这这样做.
    C 如果在需要创建一条新纪录的时候,对auto_increment 数据列明确制定了一个数,会有两种情况存在:1,某个纪录已经使用了这个数,此时Mysql 会报告错误.2,如果没有纪录使用这个数,那么纪录正常插入,以后再插入的新纪录就会以这个编号+1开始!也就是说,我们可以人为地”跳过”一些编号,举例来说,如果插入一个”假”纪录,编号为999 那么以后的纪录就会从1000开始.然后我们就可以删除”假”纪录了.这是个简单的技巧.
    D 如果删除了编号最大的纪录.再插入新纪录的时候仍然会使用这个编号!这是ISAM表本身的特点,也就是说,如果把数据表里面的纪录全部干掉的话,新纪录编号将从1开始
    E last_insert_id() 函数能够返回编号序列中最后一个生成的编号.在某些情况下,我们可以利用这个函数取得当前数据表中最后一个生成的编号.

    下面要说的是Mysql 里面的,目前大部分用户使用的 MyISAM表类型对auto_increment 数据属性的处理方法,可以看到,这种新的表类型有更大的灵活性,消除了原始的IASM标在处理序列和编号方面的许多缺陷!下面我们看:
    A 在MyIASM表里面,一个自动生成的序列编号将严格地依次递增,即使被删掉也不会被再次使用!
    B 可以在建立数据表的时候,使用 auto_increment=n 选项为序列编号明确地设定一个初始值
    C 可以用alter table 随时改变MyISAM表中auto_increment的开始值,比如,下面这条命令会让序列编号从2000开始

    其他注意事项

    1,Mysql 提供auto_increment的主要目的是生成一个正整数数据列,所以我们可以把该列声明为unsigned ,这样能够使用的编号范围就多了一倍
    2, auto_increment 属性仍然受到具体的数据列的取值范围的限制,比如一个tinyint数据列,编号最大值也只能是127
    3,清除一个表的全部内容会让序列从1开始重新生成.

    最后的问题

    当我们打算创建一个数据表的时候,面对这么多的数据列属性,如果做出正确的选择?

    A这个数据列将用来存放哪一种数据?

    这是我们第一个需要考虑的问题，一般说来，这个问题的答案似乎很直观：把数值放到数据列里面；把字符串放到字符串列里面；如果是整数就用整数列，如果有小数就用浮点类型。但是，事情往往不是这么简单，只有洞察有关数据的本质，才能明智地选择出最佳类型，有时候的结论往往是这样：最不适合人类阅读和理解的方案往往是最适合数据库的！这是不是很有趣，总之，对数据的全面把握才是选择数据列的关键所在。大家在学习过程中慢慢的体会这一点吧！

    B数据值是否都在某个具体的范围内?

    在第一章，我们为考试积分项目创建了一个名为score 的数据表，其中有一个用来记录考试和测验成绩的score 数据列，当时我们使用了int 类型，现在我们考虑到了对于考试来说，成绩一般都在0-100之间，因此我们用 tinyint unsigned 是不是更合适呢？但是如果有一种特殊情况，那就是校方会用-1来表示某个学生因病没有来考试，这时unsigned 就不行了

    C有没有性能和效率方面的问题?

    一般来说：
    数值操作比字符串快
    近可能地使用enum类型,因为这种类型在MySQL内部是用数值表示的，速度较快！
    小类型的处理速度比大类型快

    D打算如何对有关数据进行比较?
    E是否要在某个数据列商建立索引?

    这两个问题往往看似简单，但对于比较特殊的应用来说值得相当关注！

    有关数据类型转换的问题

    这个问题是很重要的！尤其对于那些用来删改数据记录的delete和update语句，必须确保这些语句只能施加在想对之进行操作的那些记录上！举例来说，假设某个数据表中有这么一个char数据列
‘abc’
‘def’
‘00’
‘jk0’
    看这个命令 delete from table_name where char=00
    我们的本意可能是想把包含有00那条记录删掉，可实际上本命令执行之后会把所有的记录全部赶掉！原因就在于MySQL把00当作了一个数值（字符串和数值比较时，比较操作将把他的两个操作数都当作数值来看待，这种情况下，所有的字符串都不可避免的变成了0）

    所以我们在执行delete语句之前一定要先用select 检查一下 where 带来的结论。

    下面是最常见的类型转换功能，大家好好学学
    1) 把数据加上一个0或0.0 能把它强制转换为数值型
    2) floor函数会把浮点数强制转换为整数
    3) 给一个整数加上一个0.0会强制转换为浮点数
    4) concat函数能把任何类型强制转换为字符串
    5) hex函数能把字符串强制转换为十六进制数
    6) ascii函数能把字符转换为ascii码
    7) data_add函数能够把字符串或者数值转换为日期和时间值
    8) 给日期和时间值加上一个0,可以强制转换为数值

    2004年4月21日