Ivita里使用Mysql的一些小技巧

August 2, 2016 One-minute read

项目背景

因为旅行计划与放假安排没有完全重叠，于是向老板申请调假，在小伙伴们都放高温假的时候，在实验室干活一周。于是被临时安排到了这个叫做Ivita项目里，做的东西其实就是一个手环app。不过因为是临时一周，之后出现问题不好解决，所以只负责了后端的几个API实现。技术框架原本就已经定好，slimePHP + Medoo，数据库是Mysql。然后由于懒癌发作，加上很多功能Medoo并不能实现，于是用了不少以前没有用过的SQL语句和设置。

Table设置

Create和Update的自动记录时间触发

这个其实在用Postgresql的时候就已经用过了，结果发现Mysql更加简单。

	created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

多主键限制

Ivita里uid和date共同作为主键，所以需要给他们俩一起添加一个唯一性限制。

	UNIQUE KEY (uid, date)

SQL语句

分组求和并排序

业务需求本来求和周年月的每日数据，然后返回一个排名，不过之后这个功能没有意义了，就没有用到这个功能了。这里贴一个更好的：

	SELECT orderNumber,
	       FORMAT(SUM(quantityOrdered * priceEach),2) total
	FROM orderdetails
	GROUP BY orderNumber
	ORDER BY SUM(quantityOrdered * priceEach) DESC;

插入重复则更新

很方便的一个功能，本来要写两条还要写代码判断的现在一句话就能解决了，不过Ivita用的Mysql版本不支持Json，所以不知道如果有的字段是Json的话，是不是依然还是那么方便。在Ivita里我把Json字段单独处理的。

	insert into steps (uid, date, count, calory, kilo) values (${id}, ${value}, ${data['count']}, ${data['calory']}, ${data['kilo']}) on duplicate key update count=count+values(count), calory=calory+values(calory), kilo=kilo+values(kilo);