當(dāng)操作mysql出現(xiàn)如下錯(cuò)誤時(shí)：
[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
需要在語(yǔ)句中輸入
set sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES';
示例：創(chuàng)建儲(chǔ)存過(guò)程函數(shù) update_ratio()衣屏。
CREATE PROCEDURE update_ratio()
BEGIN
set sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES';
--當(dāng)所有數(shù)據(jù)都需要更新時(shí)，可以用replace into 進(jìn)行數(shù)據(jù)插入 這里把jinseqt表查找到的符合條件的數(shù)據(jù)，生成臨時(shí)表bb育八，并將數(shù)據(jù)插入到新表updowninfo仲锄；同時(shí)支持更新時(shí)替換同一主鍵數(shù)據(jù)侨颈。
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 中可以通過(guò)事件來(lái)自動(dòng)觸發(fā)該函數(shù)的執(zhí)行求泰，除了信號(hào)功能來(lái)觸發(fā)外卜朗，還可以通過(guò) pipelines.py 來(lái)實(shí)現(xiàn)，這里在關(guān)閉數(shù)據(jù)庫(kù)前執(zhí)行該函數(shù)
def close_spider(self, spider):
self.cursor.callproc('update_ratio') # 調(diào)用mysql 儲(chǔ)存過(guò)程函數(shù)
self.db.commit() # 完成提交
self.db.close()