mysql 操作知识点

1.setInc一直返回0

UPDATE `tpshop_seller_goods` SET `sall_numbe`=sall_numbe+1 WHERE ( goods_id=162 )

返回值一直是0

原因是数据表中sall_numbe，没有默认值0，添加第一个数据时是空，空加1为空。

2.SQL left join 左表合并去重技巧总结

https://www.cnblogs.com/candlia/p/11920037.html

DISTINCT

SELECT
	DISTINCT(a.id), a.*,
	b.type 
FROM
	table1 a
	LEFT JOIN table2 b ON a.sponsor_id = b.sponsor_id 
WHERE
	b.type = 1 
	AND a.sponsor_id = 10;

GROUP BY 

SELECT 
	a.*,
	b.type 
FROM
	table1 a
	LEFT JOIN ( SELECT * FROM table2 GROUP BY sponsor_id ) AS b ON a.sponsor_id = b.sponsor_id 
WHERE
	b.type = 1 
	AND a.sponsor_id = 10;

 max取唯一

SELECT
	a.*,
	b.type 
FROM
	table1 a
	LEFT JOIN ( SELECT MAX( kid ), type, sponsor_id FROM table2 GROUP BY sponsor_id ) AS b ON a.sponsor_id = b.sponsor_id 
WHERE
	b.type = 1 
	AND a.sponsor_id = 10;

in

SELECT
	a.* 
FROM
	table1 a 
WHERE
	a.sponsor_id IN ( SELECT sponsor_id FROM table2 WHERE type = 1 AND sponsor_id = 10 );
SELECT
	a.*,
	1 
FROM
	table1 a 
WHERE
	a.sponsor_id IN ( SELECT sponsor_id FROM table2 WHERE type = 1 AND sponsor_id = 10 );

3.Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 't.road_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

解决办法：

找到MySQL的配置文件

找到服务器上面 /etc/my.cnf文件，编辑

在文件中加入一句：

sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

参考：https://blog.csdn.net/hechenhongbo/article/details/106951386

https://www.cnblogs.com/yangfei123/p/10178427.html