随笔分类 - Sql
摘要：-- 第一个会话执行 drop table if exists test1; create table test1( id int not null ,name int ,primary key(id) ,unique key(name) ) engine=innodb ; begin; inser
阅读全文
摘要：select replace(convert(info using ascii),"?","") as info_fix ,convert(info using ascii) as info_fix2 ,substring_index(substring_index(INFO,'\n',2),'\n
阅读全文
摘要：select id ,json_extract_scalar(json_item,'$.user_id') as user_id ,json_extract_scalar(json_item,'$.url') as user_id from ( select A.id ,concat('{',rep
阅读全文
摘要：### 导出化建表语句 mysql -hDB_NAME -uUSERNAME -pPASSWORD -A -N DATABASE -e " show create table table_name_01 ; show create table table_name_02 ; " > show_cre
阅读全文
摘要：问题原因： 1 过去mysql密码认证插件是 mysql_native_passwd 2 mysql8.0版本以后密码认证插件使用的是caching_sha2_password 办法： 修改密码认证方式，改回mysql_native_passwd插件 解决： 1 vim /etc/my.cnf添加如
阅读全文
摘要：date_add和date_sub 语法为：date_add(date,interval expr type)、date_sub(date,interval expr type) 其中常用的type的类型有：second、minute、hour、day、month、year等 -- 获取日期 202
阅读全文
摘要：select day ,case when month(day) != month(date_add(day,7)) then 'y' else 'n' end as is_last_week -- 是否月份最后一周 from ( select '2021-06-06' as day union a
阅读全文
摘要：DELIMITER $ CREATE TRIGGER `dw_realtime_i_a` AFTER INSERT ON `dw_realtime` FOR EACH ROW begin replace into dw_realtime(id,etl_dt) select id ,DATE_FORM
阅读全文
摘要：### ADB测试 ### RDS测试 https://blog.csdn.net/marising/article/details/105974151
阅读全文
摘要：select * from ( select '1 ' as table_name,count(1) as cnt union all select '2 ' as table_name,count(1) as cnt union all select '3 ' as table_name,coun
阅读全文
摘要：### 1. 报错："PDOException: PDO::__construct(): The server requested authentication method unknown to the client [caching_sha2_password] 问题原因： 1 过去mysql密
阅读全文
摘要：-- 检查表的元数据是否有异常 -- 判断逻辑为如果表有记录，但table_rows为0则异常 select t2.table_name ,t1.cnt ,t2.table_rows ,case when t1.cnt = 10 and t2.table_rows = 0 then 1 else 0
阅读全文
摘要：MySQL [DB_NAME]> desc TABLE_NAME; | status | enum('待派单','待指派','待整改','已整改','已通过','被退回','已作废','非正常关闭') | YES | | NULL | | select status+0 as status_num,
阅读全文
摘要：命令格式 WITH cte_name AS ( cte_query ) [,cte_name2 AS ( cte_query2 ) ,……]
阅读全文
摘要：2020-07-24 11:23:12.145 [0-0-0-writer] WARN CommonRdbmsWriter$Task - 回滚此次写入, 采用每次写入一行方式提交. 因为:[9001, 2020072411231201011105516903453695186] unsupport
阅读全文
摘要：这个方案是之前一个前同事问我时想出来的，但当时没进行实践，只是觉得可行。 今天终于也遇到这个场景，所以就进行测试。 两个库进行测试，还有其它的方案，比如spark，python，落地文件再对比差异等，各有优劣，比如spark需要集群的支持，并且速度也不是很快。ptyhon对大数据量的支持不是很好，落
阅读全文
摘要：-- rds5.6 执行此类SQL导致数据库崩溃 select * from infomation.tables where table_shema='cc' and table_name in('XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
阅读全文
摘要：with recursive cte(n) as ( select 1 as n union all select n + 1 from cte where n<5 ) select n from cte ; https://www.cnblogs.com/songgj/p/10658916.htm
阅读全文
摘要：with data as ( select t1.* ,row_number() over(partition by id order by end_date desc) as rn from ( select 1 as id,20 as status,'2020-03-28' as start_d
阅读全文
