Unknown column 'date_joins.tv' in 'on clause'

SELECT tv AS '日期'
,(SELECT COUNT(1) FROM user_center.`project_users` pu
INNER JOIN (
SELECT DISTINCT use_uid FROM course.`course_order_items`
WHERE pid=1 AND state=1 AND price>1
AND complete_at>=date_joins.tv 
AND complete_at<date_add(date_joins.tv,interval 1 month)
UNION 
SELECT DISTINCT uid FROM common.`virtual_good_orders`
WHERE pid=1 AND state=2 AND price_total>2000
) b ON pu.pid=1 AND pu.uid=b.use_uid 

and pu.pro_created_at>=date_joins.tv AND pu.pro_created_at<date_add(date_joins.tv,interval 1 month)) AS '学习人数'
FROM report.`date_joins` WHERE ty='d' AND tv>='2020-01-01' AND tv<now() and tv like '%-%-01'

SELECT tv AS '日期'
,(SELECT COUNT(1) FROM user_center.`project_users` pu
INNER JOIN (
SELECT DISTINCT use_uid FROM course.`course_order_items`
WHERE pid=1 AND state=1 AND price>1
AND complete_at>=date_joins.tv 
AND complete_at<date_add(date_joins.tv,interval 1 month)
UNION 
SELECT DISTINCT uid FROM common.`virtual_good_orders`
WHERE pid=1 AND state=2 AND price_total>2000
) b ON pu.pid=1 AND pu.uid=b.use_uid 

-- 下面的那个 WHERE 原来是AND, 改为where就可以执行了
WHERE pu.pro_created_at>=date_joins.tv AND pu.pro_created_at<date_add(date_joins.tv,interval 1 month)) AS '学习人数'
FROM report.`date_joins` WHERE ty='d' AND tv>='2020-01-01' AND tv<now() and tv like '%-%-01'

第二条语句就可以执行, 嵌套字段的使用不可以用于条件中的 ON 里面的 AND, 可以 ON 其中的部分AND改为 WHERE

• 连接数过多

SHOW PROCESSLIST;

修改最大连接数，但是这不是一劳永逸的方法，应该要让它自动杀死那些sleep的进程。
SHOW VARIABLES LIKE "max_connections";
SET GLOBAL max_connections=1000;

这个数值指的是mysql在关闭一个非交互的连接之前要等待的秒数，默认是28800s
SHOW GLOBAL VARIABLES LIKE 'wait_timeout';
SET GLOBAL wait_timeout=300;

• 修改my.cnf配置注意事项

[mysqld]
max_connections=1000 # 一定要放在 [mysqld] 下面

• 异常连接杀进程

杀进程
SELECT *,CONCAT('KILL ',id,';') FROM information_schema.processlist WHERE USER='root' AND `TIME`>60;

SELECT *,MD5(INFO) AS md5_info FROM information_schema.processlist WHERE INFO IS NOT NULL 
AND COMMAND!='Sleep' AND TIME>0 AND INFO NOT LIKE '%information_schema.processlist%'
ORDER BY TIME DESC

SELECT CONCAT('kill ',ID,';') AS '杀进程' FROM information_schema.processlist WHERE INFO IS NOT NULL 
AND COMMAND!='Sleep' AND TIME>5 AND INFO NOT LIKE '%information_schema.processlist%'
ORDER BY TIME DESC

Mysql基本函数

# 判断123是否为数值,如果是则返回0否则返回1
SELECT ('123' REGEXP '[^0-9.]');

# GROUP_CONCAT 指定分隔符
GROUP_CONCAT(uid SEPARATOR ';\n')

# GROUP_CONCAT 有长度限制,默认1024
SET GLOBAL group_concat_max_len=102400;
SET SESSION group_concat_max_len=102400;

清理包含指定条件的清理sql生成

SELECT delete_sql as '-- delete_sql' FROM (
SELECT distinct concat('DELETE from `',c.TABLE_SCHEMA,'`.`',t.TABLE_NAME,'` WHERE `pid`=1 AND `uid`=222;') AS 'delete_sql',count(1) as num
FROM information_schema.`TABLES` t
LEFT JOIN information_schema.`COLUMNS` c ON t.TABLE_NAME=c.TABLE_NAME AND t.TABLE_SCHEMA=c.TABLE_SCHEMA
WHERE t.TABLE_SCHEMA != 'mysql' AND t.TABLE_SCHEMA != 'sys' AND t.TABLE_TYPE='BASE TABLE' AND c.COLUMN_NAME in ('pid','uid')
GROUP BY delete_sql
HAVING num=2
ORDER BY
c.TABLE_SCHEMA,c.TABLE_NAME,c.ORDINAL_POSITION
) aaaa

所有表字段信息

SELECT
    C.TABLE_SCHEMA AS '库名',
    T.TABLE_NAME AS '表名',
    C.COLUMN_NAME AS '列名',
    T.TABLE_COMMENT AS '表注释',
    C.COLUMN_COMMENT AS '列注释',
    C.ORDINAL_POSITION AS '列的排列顺序',
    C.COLUMN_DEFAULT AS '默认值',
    C.IS_NULLABLE AS '是否为空',
    C.DATA_TYPE AS '数据类型',
    C.CHARACTER_MAXIMUM_LENGTH AS '字符最大长度',
    C.NUMERIC_PRECISION AS '数值精度(最大位数)',
    C.NUMERIC_SCALE AS '小数精度',
    C.COLUMN_TYPE AS 列类型,
    C.COLUMN_KEY 'KEY',
    C.EXTRA AS '额外说明'
FROM
    information_schema.`TABLES` T
LEFT JOIN information_schema.`COLUMNS` C ON T.TABLE_NAME = C.TABLE_NAME
AND T.TABLE_SCHEMA = C.TABLE_SCHEMA
WHERE
    T.TABLE_SCHEMA != 'mysql'
ORDER BY
    C.TABLE_NAME,
    C.ORDINAL_POSITION;