Mysql之开发技巧

1.　　开发技巧

　　　　1.　　处理重复值

　　　　　　　　1.　　使用主键或唯一索引来防止出现重复的记录

　　　　　　　　2.　　使用insert ignore语句，如果插入的记录与现存的记录不冲突，则正常插入；如果有冲突，那么insert ignore将会告诉mysql丢掉这条记录　　　　　　　　　　　

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES ( 'Jay', 'Thomas');

　　　　　　　　3.　　使用replace语句，如果记录是新的，那么等同于insert。如果插入的是一个重复的记录，那么新纪录将会替换旧的记录

mysql> REPLACE INTO person_tbl (last_name, first_name) VALUES ( 'Ajay', 'Kumar');
Query OK, 1 row affected (0.00 sec)

　　　　　　　　4.　　删除表中的重复记录

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl GROUP BY (last_name, first_name);
mysql> DROP TABLE person_tbl;
Mysql> ALTER TABLE tmp RENAME TO person_tbl;

　　　　　　　　5.　　删除重复数据

DELETE t1 FROM table1 AS t1 JOIN table1 AS t2 ON t1.id>t2.id AND t1.name=t2.name;

　　　　2.　　分页算法

　　　　　　　　1.　　添加索引

　　　　　　　　　　　　索引只是起到部分作用

　　　　　　　　2.　　限制用户所看到的页

　　　　　　　　　　　　只提供最新的几页，上一页，下一页，1000页或10000页做个估算值即可。

　　　　3.　　处理NULL值

　　　　　　　　1.　　NULL的概念

　　　　　　　　　　　　NULL与空字符串不一样

　　　　　　　　　　　　NULL值与其他任何值的比较永远都不会为真

　　　　　　　　　　　　NULL操作符：IS NULL或者IS NOT NULL

　　　　　　　　2.　　友好显示NULL值

　　　　　　　　　　　　1.　　使用IFNULL函数

SELECT SUM (ifnull(size,0)) FROM table_a;
IFNULL(expr1， expr2)函数： 假如expr1不为NULL， 则IFNULL（ ） 的返回值为expr1； 否则其的返回值为expr2。
IFNULL（ ） 的返回值是数字还是字符串取决于其所使用的语境。　

　　　　　　　　　　　　2.　　使用IF函数

SELECT SUM (IF (size is null, 0, size)) AS totalsize FROM table_a;
IF(expr1， expr2， expr3)： 如果expr1是TRUE， 则IF()的返回值为expr2； 否则返回值为expr3。 IF()的返回值是数字还是字符串
视其所在的语境而定。

　　　　　　　　建议字段尽量避免使用NULL值　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　

　　　　4.　　存储URL地址

　　　　　　　　1.　　按照字符颠倒的方式进行存储

com.fabulab.marcomacaco
com.fabulapps.kiko
com.fandora9.angryvirus

　　　　　　　　2.　　对URL值做一个散列

SELECT CONV (RIGHT(MD5(‘http://www.mysql.com/’), 16), 16, 10) AS HASH64;
SELECT id FROM url WHERE url_hash=CONV(RIGHT(MD5('http://www.mysql.com/'), 16), 16, 10) AND url=http://www.mysql.com;　　

　　　　5.　　归档历史数据

　　　　　　　　1.　　把过期的历史数据放到其他性能较差的实例

　　　　　　　　2.　　按照时间分表

　　　　　　　　3.　　一般把归档操作的逻辑放到程序处，也可以使用一些特定的归档工具来归档数据

　　　　6.　　使用数据库存储图片

　　　　　　　　MySQL BLOB类型（MEDIUMBLOB， 最大支持16MB的数据） 对于绝大部分图片来说都足够了， 我们可以使用LOAD_FILE()方法读取一个文件，

　　　　　　　　然后将内容保存到BLOB列中。

　　　　7.　　多表UPDATE　　　　　　　　

　　　　　　　　1.　　假定我们有两张表， 一张表为product表， 存放产品信息， 其中有产品价格列price； 另外一张表是product_price表， 要将product_price表中的价格字段price更新为product表中价格字段price的80%。　　　　　　　　　　　

第一种方法：
UPDATE product p, product_price pp SET pp.price = p.price * 0.8 WHERE p.productId = pp.productId;
第二种方法：
UPDATE product p
INNER JOIN product_price pp
ON p.productId = pp.productId
SET pp.price = p.price * 0.8 ；

　　　　　　　　2.　　也可以使用LEFT JOIN来做多表UPDATE， 如果product_price表中没有产品价格记录的话， 将product表的isDeleted字段设置为1

UPDATE product p
LEFT JOIN product_price pp
ON p.productId = pp.productId
SET p.deleted = 1
WHERE pp.productId IS NULL;

　　　　　　　　3.　　上面的几个例子都是在两张表之间做关联， 但是只更新一张表中的记录， 其实MySQL是可以同时更新两张表的， 如
下查询就同时修改了两个表

UPDATE product p
INNER JOIN product_price pp
ON p.productId = pp.productId
SET pp.price = p.price * 0.8,
p.dateUpdate = CURDATE ();