<?xml version="1.0" encoding="utf-8" ?><rss version="2.0"><channel><title><![CDATA[chengangcsdn的博客]]></title><description><![CDATA[]]></description><link>https://blog.youkuaiyun.com/chengangcsdn</link><language>zh-cn</language><generator>https://blog.youkuaiyun.com/</generator><copyright><![CDATA[Copyright &copy; chengangcsdn]]></copyright><item><title><![CDATA[更新转到简书]]></title><link>https://blog.youkuaiyun.com/chengangcsdn/article/details/115858087</link><guid>https://blog.youkuaiyun.com/chengangcsdn/article/details/115858087</guid><author>chengangcsdn</author><pubDate>Mon, 19 Apr 2021 10:00:22 +0800</pubDate><description><![CDATA[更新已转到简书，喜欢数据库技术请查看我的简书首页
https://www.jianshu.com/u/03daa764daf0

]]></description><category></category></item><item><title><![CDATA[mysql 树型表 通过儿子节点找所有父级的方法]]></title><link>https://blog.youkuaiyun.com/chengangcsdn/article/details/112961247</link><guid>https://blog.youkuaiyun.com/chengangcsdn/article/details/112961247</guid><author>chengangcsdn</author><pubDate>Thu, 21 Jan 2021 16:04:51 +0800</pubDate><description><![CDATA[-- 构造测试数据2
CREATE TABLE `treetable` (
`id` int NOT NULL ,
`name` varchar(50) NOT NULL ,
`parid` int NOT NULL ,
PRIMARY KEY (`id`)
)
;




insert into treetable
select 1,'1',0 union all
select 2,'1-1',1 union all
select 3,'1-1-1',2 union all
select 4,'1-1-.]]></description><category></category></item><item><title><![CDATA[mysql  树形表 通过父节点找出所有儿子的方法]]></title><link>https://blog.youkuaiyun.com/chengangcsdn/article/details/112961003</link><guid>https://blog.youkuaiyun.com/chengangcsdn/article/details/112961003</guid><author>chengangcsdn</author><pubDate>Thu, 21 Jan 2021 16:03:38 +0800</pubDate><description><![CDATA[-- 构造测试数据2
CREATE TABLE `treetable` (
`id` int NOT NULL ,
`name` varchar(50) NOT NULL ,
`parid` int NOT NULL ,
PRIMARY KEY (`id`)
)
;




insert into treetable
select 1,'1',0 union all
select 2,'1-1',1 union all
select 3,'1-1-1',2 union all
select 4,'1-1-.]]></description><category></category></item><item><title><![CDATA[mysql 字符串分割列转行 实例操作]]></title><link>https://blog.youkuaiyun.com/chengangcsdn/article/details/112788401</link><guid>https://blog.youkuaiyun.com/chengangcsdn/article/details/112788401</guid><author>chengangcsdn</author><pubDate>Mon, 18 Jan 2021 16:46:18 +0800</pubDate><description><![CDATA[问题
https://bbs.youkuaiyun.com/topics/398911039
表数据如下
ID  TYPE         T1   T2   T3   T4
1  上海,北京    S     B     S     B
想查询成下面这样的
ID TYPE   T1        T2          T3           T4
1  上海     S          NULL     S             NULL
1  北京     NULL   B            NULL ]]></description><category></category></item><item><title><![CDATA[mysql 性能优化之exists 写法不一样，性能提升400倍]]></title><link>https://blog.youkuaiyun.com/chengangcsdn/article/details/112269221</link><guid>https://blog.youkuaiyun.com/chengangcsdn/article/details/112269221</guid><author>chengangcsdn</author><pubDate>Wed, 06 Jan 2021 14:13:41 +0800</pubDate><description><![CDATA[业务需求查询订单中包含商品《高性能 mysql》的单据总量

上代码

select count(1) from saleorder a
            where a.tid = 180571  and exists(select 1 from saleorderdetail e1 where a.tid = e1.tid and a.billid = e1.billid and e1.goodsid in (1061)



执行时间为 16s

mysql...]]></description><category></category></item><item><title><![CDATA[My SQL 大批量本地导入 / 阿里云 rds mysql 本地导入]]></title><link>https://blog.youkuaiyun.com/chengangcsdn/article/details/112036542</link><guid>https://blog.youkuaiyun.com/chengangcsdn/article/details/112036542</guid><author>chengangcsdn</author><pubDate>Thu, 31 Dec 2020 17:43:09 +0800</pubDate><description><![CDATA[下面用到的工具主要是两个一个是 mysql workbench另一个是免费的 DBeaver

导入

自建服务器肯定选择服务器文件导入，但阿里云的 rds不行。因为没有服务器文件权限。所以只能本地导入

第一步查看local_infile全局如果没有打开则打开

show global varaiables like ‘local_infile’;

我的的显示为OFF

进入阿里云参数修改界面将值改为true

然后执行导入

LOAD DATA local INFILE 'c:\\a...]]></description><category></category></item><item><title><![CDATA[mysql 捕获异常实例]]></title><link>https://blog.youkuaiyun.com/chengangcsdn/article/details/110860101</link><guid>https://blog.youkuaiyun.com/chengangcsdn/article/details/110860101</guid><author>chengangcsdn</author><pubDate>Tue, 08 Dec 2020 11:05:22 +0800</pubDate><description><![CDATA[delimiter ;;
create procedure testhanderdemo(out ret int )
begin
 DECLARE EXIT HANDLER FOR SQLEXCEPTION --当有异常退出并将ret设为 -1
 begin
  set ret = -1;
 end;
 set ret = 0;
 insert into t1(id,a,b) values(1,1,1); --此语句会造成主键重复的报错
end
delimiter ;

call te...]]></description><category></category></item><item><title><![CDATA[mysql写法 在clickhouse中的实现方法。 持续更新中..]]></title><link>https://blog.youkuaiyun.com/chengangcsdn/article/details/108223178</link><guid>https://blog.youkuaiyun.com/chengangcsdn/article/details/108223178</guid><author>chengangcsdn</author><pubDate>Tue, 25 Aug 2020 16:02:31 +0800</pubDate><description><![CDATA[1、group_concat用 groupArray代替；

例：

SELECT * FROM limit_by

┌─id─┬─val─┐
│ 1 │ 10 │
│ 1 │ 11 │
│ 1 │ 12 │
│ 2 │ 20 │
│ 2 │ 21 │
└────┴───┘

SELECT
  id,
  groupArray(val)
FROM limit_by
GROUP BY id

┌─id─┬─groupArray(val)─┐
│ 2 │ [20,21]   ...]]></description><category></category></item><item><title><![CDATA[centos 安装clickhouse 并从 mysql 导入数据 (亲测成功！)]]></title><link>https://blog.youkuaiyun.com/chengangcsdn/article/details/108195840</link><guid>https://blog.youkuaiyun.com/chengangcsdn/article/details/108195840</guid><author>chengangcsdn</author><pubDate>Tue, 25 Aug 2020 11:25:17 +0800</pubDate><description><![CDATA[1、创建 clickhouse目录

mkdir clickhouse
2、进入到clickhouse

cd clickhouse
3、下载clickhouse 有两个版本可供下载详情可参见https://github.com/Altinity/clickhouse-rpm-install

curl -s https://packagecloud.io/install/repositories/altinity/clickhouse/script.rpm.sh | sudo bash
看...]]></description><category></category></item><item><title><![CDATA[mysql min函数优化]]></title><link>https://blog.youkuaiyun.com/chengangcsdn/article/details/107966243</link><guid>https://blog.youkuaiyun.com/chengangcsdn/article/details/107966243</guid><author>chengangcsdn</author><pubDate>Wed, 12 Aug 2020 20:43:01 +0800</pubDate><description><![CDATA[mysql min函数优化

select min(id) from table where noindexfield = 'abc'

当where条件未有索引，求的最小字段又是主键的时候，可采用

select id from table wherenoindexfield = 'abc' limit 1



优化方案出自高性能mysql
...]]></description><category></category></item><item><title><![CDATA[mysql 增加自定义行号的方法。]]></title><link>https://blog.youkuaiyun.com/chengangcsdn/article/details/107412502</link><guid>https://blog.youkuaiyun.com/chengangcsdn/article/details/107412502</guid><author>chengangcsdn</author><pubDate>Fri, 17 Jul 2020 16:54:13 +0800</pubDate><description><![CDATA[select id,@id1 := @id1 +1 as tt,@id2 := @id2 + @id1 as abc from tenant,(select @id1 :=0) as t1,(select @id2:= 100) as t2

结果集

70535950635106304
			1
			101
		70937194633625600
			2
			103
		72406426726694912
			3
			106
		72641808848388096
			4
			110
		7]]></description><category></category></item><item><title><![CDATA[sql server  DATETIME类型的坑]]></title><link>https://blog.youkuaiyun.com/chengangcsdn/article/details/107386202</link><guid>https://blog.youkuaiyun.com/chengangcsdn/article/details/107386202</guid><author>chengangcsdn</author><pubDate>Thu, 16 Jul 2020 16:32:53 +0800</pubDate><description><![CDATA[datetime类型的精度为 ：千分之三秒（3.33毫秒、0.00333秒）。所有的datetime类型的值在显示、处理时 有所调整。即会圆整到几个特殊的毫秒个位值：0、3、7：如：(9、0、1） 会引起进0调整；（5、6、7、8）引起7调整；（2、3、4）引起3调整。

举例

DECLARE @dt DATETIME
SET @dt='2020-06-30 23:59:59.999'
SELECT @dt

----- ----

结果会变成2020-07-01 00:00:00.000

那么查询..]]></description><category></category></item></channel></rss>