函数（储存过程）mysql

当操作mysql出现如下错误时：
[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

需要在语句中输入
set sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES';

示例：创建储存过程函数 update_ratio()。

CREATE PROCEDURE update_ratio()
BEGIN

set sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES';

--当所有数据都需要更新时，可以用replace into 进行数据插入 这里把jinseqt表查找到的符合条件的数据，生成临时表bb，并将数据插入到新表updowninfo；同时支持更新时替换同一主键数据。

REPLACE INTO updowninfo
(
com_date,
up_sum,
down_sum,
ud_sum,
ud_diff,
ud_ratio
)
SELECT
cme,
upe,
downe,
sume,
diffe,
ratioa
FROM
(
SELECT
date_format(created_time, '%Y-%m-%d') AS cme,
SUM(up_counts) AS upe,
sum(down_counts) AS downe,
SUM(up_counts) + sum(down_counts) AS sume,
SUM(up_counts) - sum(down_counts) AS diffe,
SUM(up_counts) / sum(down_counts) AS ratioa
FROM
jinseqt
GROUP BY
date_format(created_time, '%Y-%m-%d')
) AS bb;
END

在python scrapy 中可以通过事件来自动触发该函数的执行，除了信号功能来触发外，还可以通过 pipelines.py 来实现，这里在关闭数据库前执行该函数

def close_spider(self, spider):
    self.cursor.callproc('update_ratio')    # 调用mysql 储存过程函数
    self.db.commit()  # 完成提交
    self.db.close()