手机浏览 RSS 2.0 订阅 膘叔的简单人生 , 腾讯云RDS购买 | 超便宜的Vultr , 注册 | 登陆
浏览模式: 标准 | 列表分类:DataBase

精通MYSQL数据库——连载十二

三大范式:第一范式,第二范式,第三范式,听着名字就很恐怖。但其实现在的人都被这个所谓的第三范式折腾死了,有事没事就拿出来涮涮,究竟怎么理解这些呢?一个一个慢慢的介绍。

数据库理论家们为数据库的设计1对N,N对N这种问题总结出了一个通用的解决方案,只需一步一步地三个范式(Normal Form)的规则应用到自己的数据库上就可以了。

第一范式的规则:

1、内容相似的数据必须“消除”(所谓消除,即再创建一个表来存储他们)

2、必须为每一组相关数组分别创建一个数据表

3、每条数据记录必须用一个主键来标识

第一条规则,看上去就比较适用于1对多的情况

第二条规则就不太好控制了,很多人认为第二条规则很难理解,数据的相关度,很难简单的描述清楚

第三条规则其实是一个实践经验,它的意思是数据表里的第一个数据行都应该包括一个独一无二的标识符作为索引。在使用MYSQL的时候,我们大多采用了自增字段来作为主键,但并非只有整数的自增字段才能作为索引,只要是独一无二的数据列,都可以用来做索引,之所以采用自增列,那是因为:1、不需要主动插入值2、整数列的时间和空间效率相对比其他类型的要高。

第二范式的规则:

1、只要数据列里的内容出现重复,就意味着应该把数据表拆分为多个子表

2、拆分形成的数据表必须用外键关联起来

第二范式,其实是在第一范式的基础上再进行一个拆分。指的就是第一范式规则的第二条内容。

外键关系在第二范式里显得特别重要,是因为第二范式时,数据会拆得更细,如果没有外键关联,恐怕数据就找不回来了。外键相当于我们日常所说的:交叉引用,对开发人员来说相当于指针。

第三范式的规则

第三范式只有一条规则:与主键没有直接关系的数据列必须“消除”

其实也就是把第二范式再分解,再建表。

可想而知,等到真正把一堆数据完全按照第三范式来进行设计的时候,恐怕在数据库里也就只能看到一堆ID了,数据呢?数据在哪里?通过外键,外键的外键,外键的外键的外键来慢慢的一个一个搜索吧。

对于MYSQL 4.1以下的版本,第三范式是会要了他们的命的,foreign key功能的不完善,让MYSQL 4.1以下版本,基本不适合第三范式,能用到第二范式设计时,数据表的效率已经几乎不能保证了。

这三个范式是著名学者E.F.Codd最先提出来的,后人在此基础 上对大到数学集合理论、小到关系数据库设计细节等诸多方面进行了研究和探索。如果对这方面有兴趣的朋友,还是多找找相关的书籍看看为好。

如果你的性子比较急,那么你可以尝试按照下面的方法来进行:

1、设计数据库的时候,一定要给自己充足的时间,如果等到数据库充满了数据,而程序也几乎开发完毕时,才发现数据库设计有缺陷,那么花费的代价就太大了

2、如果发现自己创建的表的数据列有序号,如name1,name2等,那一般就意味着还有更好的解决方案没有采用。可以考虑多创建一个表,而把这些分拆开。

3、第一时间往数据库里多插点测试数据,如果发现冗余量很大,往往就是表需要分拆的信号

4、设计时应该注意数据与数据之间的关联及引用关系

5、对于设计完的数据库,应该自己尝试写SQL语句,看看能否达到你预想的目标,如果达不到,那就要考虑是否设计的有问题。

6、如果你还是等不急,根据你的需要,到网上找找有没有类似的示例数据库,可以考虑拿来作借鉴。

说了这么多时间的范式,最后再说说他们的优缺点吧

缺点:数据表的个数越多,也就相对证明了从表单里获取数据并往表里插的时候,复杂性非常大,给开发人员会带来很大的困扰。同样,表多了,查询结果时,从中提取相关数据生成查询结果的复杂性也就越大;数据库的容量随着表的拆分量的增大而增大(不过现在也不是什么矛盾了,硬盘的价格几乎也快到了白菜价了,这点可以被忽略)

优点:严格按照范式设计出来的数据库,能够提供最丰富、最灵活的查询选项,人们往往都是在等到必须使用一种新的查询或者必须对数据进行一种新的分类时才会真正意识到这一点,但可惜的是,这些新需求往往都是出现在数据库已投入运行数月之后,到时候再改数据库,代价非常大。


现在工作有点忙,连载不会忘记,但更新频率会放慢,毕竟全部都是手工打出来的字。

不会象写小说那样太监掉的,毕竟这个东西对我自己来说,也是一种学习

给自己加油,为自己打气。也谢谢大家的支持

Tags: mysql, 精通, 数据库, 连载

精通MYSQL数据库——连载十一

一个好的数据库设计应该符合以下几点要求:
1、数据表里没有重复冗余的数据(如果总是往里面插入同样的数据,那应该是设计上有问题,当然现在在利用空间换时间的时候,多数人还是保留了这样的想法)
2、数据表里没有column1,column2,column3……这类没有明确意义的字段,因为这样会让后来人摸不清头脑
3、数据表占用的空间越小越好
4、使用频率高的数据表的查询,应该都能以简单高效的方式执行(表内数据少的时候,你就是10几个left join,你可能都感觉不出什么,但数据量一大,你一个left join都会让你感觉到速度慢下来,如果最初设计时没有满足这个要求,那么,以后想改可能也没有机会了)
这些也只是一些总的原则,也只是简单的介绍,以后会详细说明,当然上面这几点并非完全正确,就象第三范式,这是一个标准,但这个标准真的就是最好的吗?并非如此,但我们现在是在讲设计数据库,它好不好,目前不讲。以后一起讨论

为MYSQL的数据库和数据表甚至字段起名字还是有讲究的,最重要的是,千万不要使用保留关键字,而且有一些单词很奇怪,在4.0里面并不是关键字,但升级后,却变成了关键字。
详细的关键字列表请看:MYSQL手册中保留字部分
对于一个完整的设计应该注意以下几项:
1、由于MYSQL对数据列的命名不区分大小写,而对库名和表名区分大小写,因此为了规范和统一,请使用同样的规则,不要象程序代码那样来个骆驼命名啥的,这样只会给开发带来困难,建议是全部采用小写,移植、升级都方便
2、不要采用特殊字符或者中文,MYSQL对于多字节的处理并不十分完美,虽然支持中文建库、建表等,但实在不建议使用,如果你的服务器对中文支持不好,可能建库的时候就会是乱码,字段里,明明看到有值就是查不出,所以,为了规范,还是采用英文,26个字母的排列组合,没有那么复杂的。
3、数据库、表、字段的长度请不要超过64个字符,
4、表名和数据列名,请尽量采用有意义的名称,不要出现上文提到的那种column1,colnmn2之类的,时间长了,你自己都可能不知道是什么意思
5、给字段命名,需要有规范,因为这样会减少粗心带来的错误,比如username,user_name,如果分在各个表里,恐怕你每次写程序的时候,都得再检查一遍吧?对于由多个单词组成的字段,要么全部加下划线,要么全部不加,这样也比较有利于开发和维护
6、数据列的单复数,原因和5一样,要么全部单数,要么全部复数,一会单数一会复数的,开发和维护的时候,你就得盯着数据表来进行了。

数据库的设计是一件很复杂的事情,要在短时间内把一批数据分割开,并存储到数据库中,还得为开发人员提供足够的优化空间,不是一天两天就能完成的,当然现在有很多这样的工具,比如powerdesign(PD),在设计完后,还能导出数据库,确实是挺方便,但这样的软件,价格就太高了,不是我们所买得起的。
平时,我们还是使用WPS的电子表格,或者openoffice的电子表格功能,设计好数据表(电子表格最大的好处就是有几乎无穷的sheet,可以让你把一个很大的库放在这些sheet里),而且,几乎每台电脑上都会有这样的工具,便于交流。这样可以在最初的阶段对于一些数据列进行安排(没办法建立索引的,只是简单的用来布局,检查设计上是否有缺陷)

对于管理MYSQL,也有很多工具,比较常见的,就是:phpMyadmin;MYSQL自已也提供过,好象是mysqlFront?记不太清,现在我自己用的是navicat for mysql lite ,不用钱的东西都是好的。虽然在国内,大家都了解软件业的行情。自己也处于软件业的下游,能够使用正版,还是使用一下正版吧。不能使用正版的,找找免费版。
在我看的书上,它介绍说openoffice里其实还隐藏着连接数据库这个功能的,而且可以能够象创建视图一样来创建SQL,这个功能不错,我以后要好好看看,如果确实有用,那我WPS也不装了,直接使用openoffice。
不过,最常用的,最方便的,还是使用phpmyadmin,它实在是居家旅行、杀人灭口之大杀器。

Tags: mysql, 精通, 数据库, 连载

精通MYSQL数据库——连载十

本来觉得二进制字段没有什么好介绍的,本来嘛,二进制字段,不就是xxxtext变成了xxxblob?长度,大小都没有变化,只是存储的类型变成了二进制,如果把那些xxxtext字段的属性加一个binary,那就和xxxblob差不多了。
只是在没有加上binary属性的时候,xxxText字段的排序和比较是按字符串类型处理,而xxxBlob是按二进制处理的。加了BINARY属性自然是一样的。
关于在数据库里存在BLOB信息,历来就是一个有争议的话题。最早的VBB论坛就是把附件存在数据库里的,后来还有人单独做HACK把附件剥出来。很多人认为数据库就应该存储一些文本信息,对于二进制数据(图像、附件等)就只应该存储链接,而把文件单独存储出去。这样可以更加有效的利用数据库的空间。
但支持把附件存储在数据库的人却认为,二进制数据存储在数据库内,有利于数据的迁移、备份,提高了数据的集成程度,而且在程序里也能够使用统一的形式访问数据。把常用的数据和这些二进制数据存储在同一个表内,一般被DBA们所痛恨,因为这会导致所有的数据记录的存取速度变慢,而且BLOB数据在正常操作情况下只能作为一个整体来读出。也就是说如果一个BLOB数据长度是1024KB,如果你仅仅想读最后那24KB的内容,没有直接的办法,只能先读出来,然后再定位到最后,BLOB数据只能以一个整体来读写和传输。
从5.0.3开始,原来的BIT型字段正式变形,成为二进制字段了,它的最大宽度达到了64,为了这个BIT型字段,MYSQL还特地增加了一条用来写出二进制数据的新语法:b'0101',SELECT查询的时候,遇到BIT型,将返回二进制数据。(我没有5.0.3的版本,没有测试过怎么读取,书上介绍,可以先使用 seelct bit_field + 0 命令把二进制转为整数,再使用select bin(bit_field + 0 ) 将这些整数显示为二进制)
对于二进制来说,数据会有溢出现象,它分为上溢出和下溢出,但不管是如何的溢出,只要是溢出了,所有的二进制位都将被设置为1。例如将-1,0,1,7,8五个数字依次存入BIT(3)数据列的时候,实际存放的是b'111',b'000',b'001',b'111',b'111'。
除开这些二进制字段,剩下的就是具有MYSQL特色的字段了,如果没有什么必要,确实不太建议使用,因为其他的数据库都不支持这两种类型,一旦在数据迁移的时候,很有可能会造成丢失或增大工作量,是什么字段呢?他们就是ENUM和SET(枚举和集合)。
对于MYSQL来说,这两种字段对于涉及字符的操作,有很高的效率,表面上存储的可能是文本,但在实际处理的时候,是按设置的下标来进行操作,即按INT类型操作,所以效率极高。
ENUM是一个字符串集合,它的成员最多可以有65535个,ENUM字 取值只能是这个集合中的某一个成员(不允许是不同成员的一个组合 ),相当于数学意义上的“排列”。
而SET虽然采用了类似的思路,但允许数据表中的SET字段的取值是集合成员的任意组合(组合数量不得超过64个),相当于数学意义上的“组合”。在内部,这些字符串分别与2的幂(1,2,4,8等)相对应,所以字符串的组合就相当于二进制位的组合 。因为每个安符串分别对应一个二进制位,所以SET类型的空间战胜比ENUM大。
虽然这两种类型的效率相对较高,但由于和其他数据库的不兼容性,因此多创建一个关联数据表来关联这些数据才是更有实用价值。


介绍完MYSQL所支持的所有字段的属性,也该介绍一些在创建数据列的时候涉及到的属性,注意,有一些属性只能用于特定的数据类型

属性名    含义
NULL    数据列可以NULL值(一般是默认设置)
NOT NULL    不允许包含NULL值(因为bTree索引不支持NULL)
DEFAULT xxx    如果输入时没有指定值,则默认以xxx为值
DEFAULT CURRENT_TIMESTAMP 这个在介绍日期时间类型的时候介绍过,默认插入当前时间
ON UPDATE CURRENT_TIMESTAMP   在数据更新的时候,自动更新为当前时间
PRIMARY KEY   定义为主键
AUTO_INCREMENT    自动输入一个序列编号,只能用于整数类型的数据列,一般与主键对应使用,必须与NOT NULL,或UNIQUE 属性同时使用
UNSIGNED    无符号整数,值得注意的是,无论怎么计算,即使是1-2,返回的也是无符号的整数
CHARACTER SET name  仅适用于字符串列,指定一种字符集和一种可选排序方式

  
虽然MYSQL有DEFAULT XXX这种属性,但实际上,MYSQL并不允许使用函数来设定默认值,比如DEFAULT rand()就不被允许了。

介绍完这些,以后就该介绍数据库的设计技巧了。之所以在介绍设计技巧之前连载了这么长时间的数据类型,是因为在设计中,使用合适的类型可以适当的增加效率,具体的还是看设计吧。

Tags: database, mysql, 连载

精通MYSQL数据库——连载九

字符串,恐怕应该算是MYSQL里面最复杂的类型了吧?几乎目前所有的问题,都是出在与字符有关的数据列上,大致有几种
1、字符串的查询(以下如果不特指,都是指中文),搜索一个中文的时候,不管是模糊还是精确,往往结果都会有与搜索内容不一致的数据在里面
2、编码,现在大家都知道MYSQL连接上后,先执行一下mysql_query('set names GBK',$conn)这类的语句,从MYSQL4.0升级到4.1及以上版本的朋友在这上面吃的苦不少了。网上关于这类的提问也是最多的
3、索引、效率,varchar是MYSQL所特有的字段,而且长度可变,char则是固定长度的字符串。
在MYSQL所支持的几个字符串格式里面,char和varchar是用的最多的,char是定长字段,,也就是说,不管字符串的实际长度有多少,CHAR(10)将永远占用10个字节。字符串如果前端有空格,那么在存储的时候会自动被数据库去掉,相当于先执行trim($string),再进行存储,如果不满10个字节,将会采用空格填满,读取数据时,MYSQL会自动将这些空格去掉。看到这里,恐怕它的缺点之一就明显的暴露了,CHAR不能存储那些结尾确实有空格的字符串(因为读取时会被MYSQL自动去掉)。


 数据类型     含义
 char(n)  固定长度的字符串,最多255个字符
 varchar(n)   可变长度字符串,最多255个字符(MYSQL<5.0.3,n<=255 , mysql>=5.0.3,n<=65535)
 tinytext      可变长度字符串,最多255
 text      可变长度字符串,最多65535-1
 medinumtext     可变长度字符串,最多16,777,216-1
 longtext      可变长度字符串,最多4,294,967,296-1

除了char类型外,其余类型均为可变长度,实际所占空间由该字段所存的内容决定。虽然varchar与tinytext一样都是255个字节,但不同的是varchar可以指定存储的长度,而tinytext只能由存储的时候数据长度而定。
在数据表被创建的时候,MYSQL 把数据列的定义改成一种对MYSQL来说更有效率的形式。这种自动方式的修改称为默许的数据列修改(client column changes)。一般来说是对CHAR和VARCHAR数据列都有影响:如果n<4,varchar(n)将被修改为char(n),而如果n>3并且在同一个数据表里还有其他的可变长度的字段,char将被修改成varchar格式。如果数据表里只有固定长度的数据列,char列将不发生变化。也就是说,你在数据表里的字段如果有多种格式,那么你通过alter table 是无法进行修改的。
上表提到,varchar在新版本里变成了65535个字节,但最大字符个数还是要取决于具体的字符集(GBK中文占两个字节,而UTF8中文则占三个字节)。上面这些所有的数据类型都有一个BINARY属性,一旦该字段添加了这个属性,那么MYSQL就会将他们视同BLOB字段来处理。BINARY的好处是在排序时把字符视为二进制,这样就可以把字母的大小写分开,而且由于是二进制字符串,所耗费的时间也会更短。可惜,在实陆应用中我们存储的大多是中文,所以这一点对我们来说并没有太大的区别。中文,本来就没有大小写之分,再怎么二进制,还是那样。呵呵。
在MYSQL4.0及以前的版本里,各种文本字段的字符集和排序方式都由MYSQL服务器决定,默认值是latin1字符集和瑞典排序方式。虽然允许在MYSQL配置文件里指定一种字符集和排序方式,但这会导致所有的数据库受到影响,还有,对字符集和排序方式做了任何修改都必须重启数据库服务器方能生效,更可恨的是,重启后,还必须重新创建所有的索引,同时,MYSQL4.0之前的版本并不支持UNICODE字符集。
4.1后,这些问题得到了很大的改状况,可以单独为某一个数据列指定一种字符集和排序方式,而且字符集和排序方式的选择范围也大了很多,因为从4.1开始支持了UNICODE和UCS-2等。与字符集及相关排序方式相关的命令是:SHOW COLLATION(必须要注意的是,字符集和排序方式不是随意组合的,每种字符集都有该字符集对应的一些排序方式,而不能混用)。如果没有为某个数据列指定一个字符集和排序方式,它将使用数据表、数据库或MYSQL数据库的默认字符集。至于数据库应该使用哪种字符类型,还是由你的项目所决定,如果你的项目采用了GBK编码,那数据库也同样使用这种编码比较好。如果你在项目初始时对于采用哪种编码未知,那么UTF8应该算是比较好的一种解决方法,数据迁移以及对于JAVA、.net等程序的支持也是最好的,唯一的缺点就是占用数据库空间太大。
说到现在,一直在说latin1和unicode字符集,这代表了什么意思呢?
latin1:在最初的MYSQL版本里都是默认采用了这种字符集,它是符合ISO-8859-1的规范的,收录了西欧国家的常用字符,在那个年代,国内还没有多少人使用MYSQL,而真正使用这些的大多是欧美国家,所以这个字符集主要就是用于收集这些国家常用的字符。随后latin的标准 在不停的变化,有latin2,latin9等,但它们当中并没有一个能够把所有欧洲语言里所有的字符都收录齐全,也就是说latin库里并没有一个可以适用于整个欧洲。
UNICODE:正由于latin不能解决这个问题,所以人们开发出了双字节的UNICODE字符集,两个字集意味着是2的16次方,可以存储65535个字符,所以UNICODE字符集不仅涵盖了所有的欧洲语言,还覆盖了绝大多数的亚洲语言。然而UNICODE只定义了每个字符的编码,没有定义如何存储这些编码。
在这个基础上,也同样发展出了好多变体。最重要的是UCS-2和UTF8,UCS-2又称UTF-16,即每种字符都用两个字节表示,所以几乎所有的字符都被涵盖了。但是这种也是有弊端的:存储空间大了一倍,即使是ASCII字符也是这样;字节编码0会出现在字符串中(比如ASCII写出来的文本里,每一个的第2个字节都是0),可是一些程序会把0当成结束标志,而导致出错。
UTF8是UTF16的替代品,在这里所有的ASCII(7位)仍然象以前一样用第一位是0的单字节表示,所有其他的UNICODE字符用2~4个字节表示。它的缺点也同样明显:同一份文档的字节数和字符数没有任何显而易见的关系,但由于它与现有的程序高度兼容,所以UTF8几乎已经成为了一种事实上的标准,而且大多数软件都把它作为自己的默认字符集。
可惜,PHP发展到现在对于UNICODE的支持还是一般性,并没有内置,处理的时候,不是用ICONV就是mb_string库(PHP6是说内置支持了),但并不能妨碍我们使用UTF8来开发网页。

字符串就介绍到这里。太累了。。。。。这个方面的问题太多了,在以后的连载中遇到再详细说明吧。

Tags: mysql, 精通, 数据库, 连载

PHP 中执行排序与 MySQL 中排序

在DBA Notes网站上发现这篇文章,也到InfoQ去看了一下,再加上这个问题确实一直是一个让人头疼的问题,由谁来处理排序更合适。利用MYSQL排序,如 果数据很大,一个order by就很有可能会让数据表锁死在那里,可是让PHP来处理,却是让数据被进行二次处理。所以看到本文的时候,就先复制过来,一来自己也可以做点备份,二来 也可以给其他人留点资料。

Q:列出在 PHP 中执行排序要优于在 MYSQL 中排序的原因?给一些必须在MYSQL中排序的实例?

列出一些 PHP 中执行排序更优的情况:

列出一些必须在 MYSQL 中排序的实例:

» 阅读全文

Tags: 排序, mysql