SQL编写小记

suzy0956 8年前

来自： https://segmentfault.com/a/1190000004438389

下面的做法大多对应与一般的情况，关于灵活性与安全性的一些做法，特殊的情况特殊处理总是有他的好处。关于SQL注入方面的内容之前已经写过一片博文了，在此不再提及

多使用交叉表（实体-关系）

经常会碰到一对多，多对多这样的逻辑关系的关系型数据库设计，特别是更多的时候是对应的数量不确定，会随着时间的推移而发生变化（比如就是说商品A有4个属性，商品B却有5个属性）。这个时候，就不应该把这种对应关系限定在一张表内，而是把这些实体分表储存，然后用一张表储存他们的对应关系，这场储存对应关系的表，就是交叉表

当在思考下面这些问题的时候，可能就需要考虑建立交叉表：

可能需要在一张表中存入一个字段，这个字段里面有好多个值，需要用逗号还是什么标记符隔开，或者还想保证不重复
列表最多能储存多少数据（受限于varchar的长度限制）
当需要用正则表达式来提取字符串中的数据
哪些字符绝对不会出现在数据中（选择合适的分隔符的时候）
一个列不能同时指定两个外键（就像这种情况：在个人网站里面既有说说又有博客，浏览者都可以在里面发表评论，这样一般来说就有三个表，说说的内容，博客的文章内容，还有评论，然后设置约束的时候，评论的id对应说说内容的id，同时也对应了博客内容的id，但是在设置评论表的外键的时候却不能设置两个外键。这时就可以设置两个交叉表）

是否真的需要id

现在好多人大多都习惯性的每建立一张新表就设置一个伪键id，来作为表的主键。声明主键是必要的，然而哪里都不分青红皂白的使用id并不是一件好事。它可能会成为冗余的键值，可能会使得你的数据库允许出现重复的项，也可能会因为id的意义不明确而带来混淆。

所以，在真正建表的时候，应该仔细考虑主键的选择问题

如果采用了伪主键，就应该起一个意义清晰的名字，比如说account_id,而不是简单粗暴的id
考虑使用自然健和组合健。当你发现你的表中有一列能唯一标识一条记录的列，可能就没有必要设置id来作为主键了，合适的时候，如果多个列的组合能够唯一的确定一条记录，也可以考虑使用组合键

多使用约束

据说有很多程序员不推荐引用完整性约束，或是说更新数据的时候可能会冲突，或是说影响性能blablabla....但是，当不引用完整性约束的时候，所付出的代价就是必须增加额外的代码来保证数据的正确，即使保证代码的逻辑严谨性是程序员必须做的事情。但是：

如果要写出一个没有瑕疵的代码，意味着进行数据操作的时候要检查所有的引用关系。如果恰好在删除操作的查询和删除操作的执行之间插入了一个新的记录，就会引发一个错误的引用关系的问题。要解决这个问题，就要在删除的时候加锁，搞完再解锁。然而这么做在高并发和大量数据查询的时候表现就会很差
或者有人会想用一个脚本来检查错误的数据。当真正发现错误的时候，下一步就是解决错误，有些问题是可以解决的，比如通过设置默认值。但是，有些时候就会面临一个很尴尬的境地，比如有个属性叫publisher.....
要写出一个真正没有瑕疵的代码，真的很难，比如说整个逻辑必须考虑的非常严谨，比如说在代码迭代的时候必须保证把每个地方都一起改对，等等...

拆分表

项目一开始的时候，可能数据库三下五除二的就设计好了。但是在开发，或者迭代的时候，可能会产生新的需求，还是说一开始的设计出现了问题，这个时候可能就需要不断的扩展这张表。还可能，随着时间的推移，数据量越来越庞大，查询耗时越來越多，这个时候可能就需要将表分区。

水平分区

当一张表的数据量非常巨大的时候，就可以水平将表分区，在建表的时候就可以做如下设定：

CREATE TABLE story(      story_id INT PRIMARY KEY AUTO_INCREMENT,      time DATE  ) PARTITION BY HASH(YEAR(time))    partitions 4;

这样就可以将实际储存数据的物理表分成4张，也不用担心数据会在错误的标里面，直接查询也能得到结果

垂直分区

当某些列非常庞大（如BLOB或TEXT）或者很少使用的时候，就可以将这列分在另外一个表里面，然后用外键建立联系

取整

当在数据库某一属性的类型是FLOAT或者DOUBLE的时候，由于二进制的原因，某个数会变成无限小数，这个时候数据库里面存的就是近似值，查询的时候救不得不限定一个合理的精度，就像这样：

SELECT * FROM account WHERE ABS（value - 60.00） < 0.000001;

有时候误差虽然很小，但是累积起来就会变得很大。

对于FLOAT和DOUBLE，其实还有更好的选择，NUMERIC 和 DECIMAL。类型的精度是创建的时候指定的，同时，他不会像FLOAT那样将存储的有理数进行舍入操作

数据库储存文件

很多时候，项目需要在服务起存储图片啊什么的这样的数据，直觉上来看，很自然的方法就是使用文件系统，然后子数据库里面用VARCHAR来存文件的路径。但是，这么做也有很多缺陷：

在数据库里面操作数据（比如说删除），删掉之后实质上文件还在
文件不支持输入
文件不支持回滚操作
文件不支持数据库悲愤工具
文件不支持SQL 的访问权限设置
文件也不是SQL数据类型（意味着数据库不能用约束去验证这个文件的正确性）

事实上，可以用BLOB类型，直接将文件存在数据库里面，像MySQL就可以这么做

UPDATE account  SET image=LOAD_FILE('images/1.jpg')  WHERE user_id = 1;

当需要取出来的时候，就可以这么做

SELECT image  INTO DUMPFILE 'images/1.jpg'  FROM account  WHERE id=1;

多次查询

SQL很强大，也富有表现力，可以一句话做很多的事情。但是，复杂的SQL查询很难写，写出来了也很容易错，所以完全可以分开多次进行查询

不使用*

使用 可以少打几个字，但是也会带来很大的问题，因为 的不可知性，可能会引发一些错误。一来可能会破会代码重构，因为他引发的一些问题比较难找。二来一次性查询的列越多，开销也越大。