mysql
从未完美过
这个作者很懒,什么都没留下…
展开
-
mysql 展开json 数组格式数据
正常Json数组是存放多个数据的,如果需要拆分开来使用,可以用如下公式SELECT * FROM pas_func_v2, JSON_TABLE ( func_atom, "$[*]" COLUMNS ( new_atom TEXT PATH '$' ) ) AS f_list可将func_atom 单元格内的内容拆分为每一行,并重新命名为new_atom如果拆分中有空值,需要做进一步处理SELECT * FROM pas_func_v2, JSON_TABLE原创 2022-03-02 14:37:11 · 4520 阅读 · 1 评论 -
MyISAM与InnoDB 的区别(9个不同点)
区别:InnoDB支持事务,MyISAM不支持,对于InnoDB每一条SQL语言都默认封装成事务,自动提交,这样会影响速度,所以最好把多条SQL语言放在begin和commit之间,组成一个事务; InnoDB支持外键,而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败; InnoDB是聚集索引,使用B+Tree作为索引结构,数据文件是和(主键)索引绑在一起的(表数据文件本身就是按B+Tree组织的一个索引结构),必须要有主键,通过主键索引效率很高。但是辅助索引需要两...原创 2022-02-17 14:14:09 · 6815 阅读 · 1 评论 -
Mysql elt()函数与 field()函数
ELT()定义和用法:返回对应位置的字符串。示例:ELT(N,str1,str2,str3,...)如果N = 1,则返回str1;如果N = 2,则返回str2,依此类推。 如果N小于1或大于参数个数,则返回NULL。 ELT是FIELD的补充。FIELD()FIELD(value, val1, val2, val3, ...)参数:此方法接受以下两个参数:value:要搜索的指定值。val1,val2,val3,...:指定要搜索的值列表。返回值:它返回给定值列表中指定值原创 2021-08-20 16:27:09 · 597 阅读 · 0 评论 -
mysql中判断字段是否包含数字或者是否为纯数字
各种场景判断字段是否包含数字select name from text where name regex '[0-9]'使用like模糊查询包含某个数字select * from text where name like '%1%'可能会筛出各种不适我们想要的,比如包含“10”的字段也会筛选出。使用mysql原生函数FIND_IN_SET查询包含某个数字select * from text where find_in_set(1,name)比like更精确一下。使用regexp正则匹配原创 2021-08-11 19:01:32 · 3895 阅读 · 0 评论 -
Mysql 如何从句子中只取出数字
如果字符串以数字开头,然后包含非数字字符,可用CAST()函数,或者通过添加一个将其隐式转换为数字0:SELECT CAST('1234abc' AS UNSIGNED); -- 1234SELECT '1234abc'+0; -- 1234如果从任意字符串中提取数字,您可以添加一个自定义函数,如下所示:DELIMITER $$CREATE FUNCTION `ExtractNumber`(in_string VARCHAR(50))RETURNS INTNO SQLB原创 2021-08-11 17:53:32 · 5404 阅读 · 1 评论 -
Mysql的INTERVAL()函数和INTERVAL关键字
INTERVAL()函数可以返回分段后的结果,语法如下:INTERVAL(N,N1,N2,N3,..........)其中,N是要判断的数值,N1,N2,N3,...是分段的间隔。这个函数的返回值是段的位置:如果N<N1,则返回0,如果N1<=N<N2,则返回1,如果N2<=N<N3,则返回2。所以,区间是前闭后开的。二,INTERVAL关键字INTERVAL关键字可以用于计算时间间隔,可以有以下用法。1,直接计算时间间隔。例..原创 2021-08-10 18:17:45 · 1263 阅读 · 0 评论 -
mysql中金融字段的设计
错误的设计规范:同财务相关的金额类数据必须使用 decimal 类型由于 float 和 double 都是非精准的浮点数类型,而 decimal 是精准的浮点数类型。所以一般在设计用户余额,商品价格等金融类字段一般都是使用 decimal 类型,可以精确到分。但是在海量互联网业务的设计标准中,并不推荐用 DECIMAL 类型,而是更推荐将 DECIMAL 转化为整型类型。也就是说,金融类型更推荐使用用分单位存储,而不是用元单位存储。如1元在数据库中用整型类型 100 存储。下面是 bigint..原创 2021-08-06 11:25:12 · 1440 阅读 · 0 评论 -
主键的设计 uuid
错误的设计规范:主键建议使用自增 ID 值,不要使用 UUID,MD5,HASH,字符串作为主键这个设计规范在很多文章中都能看到,自增主键的优点有占用空间小,有序,使用起来简单等优点。下面先来看看自增主键的缺点:自增值由于在服务器端产生,需要有一把自增的 AI 锁保护,若这时有大量的插入请求,就可能存在自增引起的性能瓶颈,所以存在并发性能问题; 自增值做主键,只能在当前实例中保证唯一,不能保证全局唯一,这就导致无法在分布式架构中使用; 公开数据值,容易引发安全问题,如果我们的商品 ID 是自转载 2021-08-06 11:22:32 · 1468 阅读 · 1 评论 -
MySQL中的外键(foreign key)
引言在MySQL中,我们都对主键比较了解,知道主键的主要作用是唯一区分表中的各个行;但是,对于外键(foreign key) 比较陌生。那么什么是外键呢?外键的作用是什么呢?一、外键、外键作用及其限制条件1.外键的定义:外键是某个表中的一列,它包含在另一个表的主键中。外键也是索引的一种,是通过一张表中的一列指向另一张表中的主键,来对两张表进行关联。一张表可以有一个外键,也可以存在多个外键,与多张表进行关联。2.外键的作用:外键的主要作用是保证数据的一致性和完整性,并且减少数据冗余。主要转载 2021-07-01 19:55:12 · 18105 阅读 · 0 评论 -
使用ClickHouse快速实现同比、环比分析 (“开窗函数“)
同比、环比分析是一对常见的分析指标,其增长率公式如下:同比增长率 =(本期数 - 同期数) / 同期数 环比增长率 =(本期数 - 上期数) /上期数 在一些提供了开窗函数的数据库中(如Oracle、Hive),可以利用lag()、lead()函数配合over(),非常方便的实现同比和环比的查询。大家知道,ClickHose目前是没有提供对应的over()函数的,但是借助一些特殊的函数,也能变相实现开窗的效果。今天就在此抛砖引玉,向大家介绍如何利用 neighbor 函数,快速实现同比、原创 2021-05-25 12:11:30 · 5213 阅读 · 5 评论 -
msyql 中SUBSTRING 和 SUBSTRING_index 用法
SUBSTRING_INDEX()函数substring_index(str,delim,count)str:要处理的字符串delim:分隔符count:分隔符计数例子取出上述表中数组的第一个元素-- SUBSTRING_INDEX(str,'"',2)取出从左往右第二个引号左边的字符串str-- SUBSTRING_INDEX(str1,'"',-1)取出从右往左数第一个引号右边的字符串str_1--SELECT SUBSTRING_INDEX(SUBSTRING_INDE.原创 2021-05-12 19:04:32 · 829 阅读 · 0 评论 -
MySQL触发器使用详解
MySQL包含对触发器的支持。触发器是一种与表操作有关的数据库对象,当触发器所在表上出现指定事件时,将调用该对象,即表的操作事件触发表上的触发器的执行。创建触发器在MySQL中,创建触发器语法如下:CREATE TRIGGER trigger_nametrigger_timetrigger_event ON tbl_nameFOR EACH ROWtrigger_stmt其中:trigger_name:标识触发器名称,用户自行指定;trigger...原创 2021-05-11 14:44:40 · 730 阅读 · 0 评论 -
mysql 通过两点经纬度计算直线距离
设有两点 A(lonA,latA) 和B (lonB,lonB)求两点之间的直线距离SET dist_distance = 6378.138 * 2 * ASIN( SQRT( POW( SIN( ( latA* PI( ) / 180 - latB * PI( ) / 180 ) / 2 ), 2 ) + COS( latA * PI( ) / 180 ) * COS( latB * PI( ) / 180 ) * POW( SIN( (lonA * PI( ) / 180 ...原创 2021-03-08 14:55:52 · 398 阅读 · 0 评论 -
mysql 8.0 1114 Error, The table /tmp/#sql~~~ is full
mysql 最近运行较大的存储过程时经常会报错,报错1114,提示:1114 Error, The table /tmp/#sql~~~ is fullmysql8.0 正好这周升级了新版本,找了国内很多解决方案都无济于事,包括调整tmp_table_size 和 max_heap_table_size 大小,清理硬盘空间,更换缓存表文件夹位置,都没有解决,最后还是公司老大找到了外网解决方案搞定。mysql 太坑了,升级这种机制按说应该及时通知的。解决方案如下调整temptab...原创 2021-02-22 18:22:45 · 9011 阅读 · 6 评论 -
mysql 的GROUP_CONCAT如何给值加上单引号后再拼接
mysql 的GROUP_CONCAT如何给值加上单引号后再拼接?可以在拼接的值名称前后添加引号和逗号解决。SELECT GROUP_CONCAT(DISTINCT '''',sales_id,'''') FROM `nb_sales` 结果: 'id1','id2','id3','id4'原创 2020-12-29 13:46:32 · 2015 阅读 · 0 评论 -
mysql 取JSON数据
正常的json使用mysql 取数据如下即可-- 查询记录SELECT sname,JSON_EXTRACT(info,'$.age') FROM t_json;SELECT sname,info->'$.age' FROM t_json;当从数组重取数据时,需要添加数组下标,-- 查询记录SELECT sname,JSON_EXTRACT(info,'$[0].age') FROM t_json;SELECT sname,info->'$[0].age' FROM .原创 2020-10-10 16:36:35 · 10089 阅读 · 0 评论 -
关于 group by 后跟别名的问题
今天在做统计时为了吧名称转换,故使用了别名,但是group by 后的结果用了别名,结果确实出来了,却没有按别名的分组;Group by不能用别名的原因,因为执行到groupby 时,还没执行到select中的别名,所以别名还没生效。所以别名只能放到比如order中,distinct中。遇到这种问题可以使用子查询替代...原创 2020-08-27 16:02:48 · 6747 阅读 · 4 评论 -
msyql 计算中位数
MySQL 不像Excel 有mid()函数直接计算中位数,需要用函数来实现具体如下:首先建立数据表create table student ( id varchar(32) primary key, value int); insert into student (id,value) values ('A',40);insert into student (id,value) values ('B',50);insert into student (id,value) value原创 2020-07-02 15:56:40 · 694 阅读 · 0 评论 -
mysql 创建字段时间和修改字段时间
mysql 创建字段时间和修改字段时间mysql 修改创建时间和修改时间字段 创建时间:alter table tablename modify updateTime timestamp no null default current_timestamp;修改时间:alter table tablename modify updateTime timestamp no null default current_timestamp on update current_timestamp;..原创 2020-06-02 16:26:14 · 1351 阅读 · 0 评论 -
Insert into select 导致的锁表
Insert into select 请慎用,同事因为使用了 Insert into select 语句引发了重大生产事故,最后被开除。某天 xxx 接到一个需求,需要将表 A 的数据迁移到表 B 中去做一个备份。他本想通过程序先查询查出来然后批量插入,但 xxx 觉得这样有点慢,需要耗费大量的网络 I/O,决定采取别的方法进行实现。通过在某度的海洋里遨游,他发现了可以使用 insert into select 实现,这样就可以避免使用网络 I/O,直接使用 SQL 依靠数据库 I/O 完成,.转载 2020-05-19 16:26:30 · 14137 阅读 · 6 评论 -
利用 force index优化sql语句性能
今天写了一个统计sql,在一个近亿条数据的表上执行,200s都查不出结果。SQL如下:select customer,count(1) c from upv_1 where created between "2015-07-06" and "2015-07-07" group by customer having c > 20 order by c desc 执行explain,发现这个sql扫描了8000W条记录到磁盘上。然后再进行筛选。type=index说明整个原创 2020-05-19 16:23:51 · 369 阅读 · 0 评论 -
mysql GROUP_CONCAT给每个值加上单引号后再拼接
经常使用group_concat拼接数值,但有一些中文在拼接时添加单引号会比较好, 该怎么操作呢?可以使用如下语句,在字段前添加四个单引号和逗号,并在字段后也添加一个引号和四个单引号SELECT Group_concat('''',tag_word,'''') from data_tag结果'data','text','word'如果想要去重再组合添加 distinct即...原创 2020-03-05 15:36:59 · 4226 阅读 · 0 评论 -
MySQL8.0新特性学习笔记(二):窗口函数
MySQL8.0引入的窗口函数,可以比较方便的实现一些分析和统计功能,这些功能不用窗口函数也能实现,不过实现的sql可能会比较复杂。一,简介什么是窗口函数窗口函数引入的其实不只是几个函数,而是一套完整的语法,窗口函数是此语法中的一部分。语法:窗口函数 over 窗口表达式。over是窗口函数语法的关键字。从语法上来看,窗口函数的使用实际上分为两部分:窗口函数和窗口。...转载 2020-01-07 10:52:03 · 278 阅读 · 0 评论 -
MySQL8.0新特性学习笔记(一):binlog复制策略优化
看一下几个版本以来binlog复制策略的演进。5.6以前的版本经典的主从复制模型:1,Master提交事务。2,binlog写入binlog文件。3,Slave的IO线程把Master上的binlog写入Slave的RelayLog。4,Slave单线程从RelayLog中读取日志并执行。瓶颈:单线程处理RelayLog太慢。5.6版本开始使用库级的并行...转载 2020-01-07 10:50:01 · 406 阅读 · 0 评论 -
mysql分组排名sql语句
建表CREATE TABLE t_rank_update(pid INT(4) PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20) NOT NULL,age INT(3),rank INT,groupid INT)插入INSERT INTO t_rank_update(NAME, age, rank,groupid)VALUES...原创 2020-01-06 21:27:14 · 514 阅读 · 0 评论 -
MySQL delete 语句
mysql 中delete语句主要分为三个层次,下面简单介绍下1. 最基础的条件删除,如下所示DELETE FROM table_name where table_name.name='dell'还有一种按序删除的方式如下DELETE FROM customers ORDER BY Name LIMIT 102.MySQL DELETE语句使用INNER JOIN...原创 2020-01-02 14:59:15 · 40896 阅读 · 1 评论 -
notepad++ 怎么查看和替换换行符
最近在做短词汇命中时,经常会发现命不中的情况,最后发现是换行符在捣鬼,因此需要用notepad++来查看和替换换行符;查看换行符在视图-显示符号-显示所有字符里,选中即可查看这里的CR LF既是换行符此时如果想要替换换行符该怎么操作呢?首选CTRL+F 调出替换,然后在查找目标里输入“\r\n” ,同时我们需要把扩展打开,然后替换为空即可。...原创 2019-12-11 12:17:59 · 16023 阅读 · 1 评论 -
MySql 错误提示 -out of range value for column
问题:之前有一组数据的id是三位数的,原类型为 smallint(5),想着就是三位数的,换乘tinyint(3)格式的,节省些空间,结果就是一直报错:out of range value for column at row 58, 仔细查了也没发现问题,最后检查发现超出了tinyint(3)的上限了,tinyint(3) 的范围是正负127,如果加上UNSIGNED,则最大为255。一旦达到...原创 2019-11-11 15:03:54 · 30876 阅读 · 1 评论 -
mysql 按照年龄段分组计数
最近在统计人群特征数据时,需要将用户年龄分组来计数,mysql 里的 case when 可以直接实现的 具体sql语句如下SELECT age, count( * ) AS cnt FROM ( SELECT CASE WHEN info_t1 <= 20 THEN '20' WHEN info_t1 <= 30 AND info_t1 >...原创 2019-11-07 14:43:43 · 3629 阅读 · 0 评论 -
关于先插入数据在维护索引 还是先加索引再插入数据
近期在更新数据库时,经常会遇到一个问题,先插入数据在加索引,还是先加索引再插入数据以下通过测试了几次得到如下结果如图:这个表大概是200多万行,加了两个组合索引,最终数据长度是190MB,索引长度是6MB,msyql8.0版本。结论如下: 在新建表结构的情况下,先加索引后插入数据会快一些;但是其他情况下如本身表格已经存有数据,在这个基础上再插入数据的情况还待下次再测试下。...原创 2019-11-06 14:31:05 · 3491 阅读 · 2 评论 -
MySQL修改表一次添加多个列(字段)和索引
MySQL修改表一次添加多个列(字段)ALTER TABLE table_name ADD func varchar(50), ADD gene varchar(50), ADD genedetail varchar(50);而索引列操作维护一次开销比较大,因此尽量也是一次维护多个索引;ALTER TABLE table_name ADD INDEX idx1 ( `func`)...原创 2019-11-06 14:14:39 · 2153 阅读 · 0 评论 -
mysql8.0版本 sum() NULL group by问题
昨天专门找了很多资料,想解决sum() 结果中带有null的问题,但昨晚最后一些列的测试发现另外的问题;表结构如下所示,id是int类型,name是varchar类型的,pct是float类型的SELECT `name`,sum(pct) FROM `test` GROUP BY `name`;SELECT `id`,sum(pct) FROM `test` GROUP BY `i...原创 2019-11-06 09:48:35 · 1339 阅读 · 1 评论 -
MySQL AVG()异常NULL值
mysql 在计算avg() 时 会自动将null值过滤掉不参与计算,因为NULL与数字不同0。从概念上讲,它NULL意味着“缺失的未知值”,并且与其他值的处理方式略有不同。这就是像ignore s 这样的聚合函数。AVG()NULLAVG()仅计算所有已知值的平均值。(=不是NULL =未知)来自MySQL文档:除非另有说明,否则组函数会忽略NULL值。另外,请阅...原创 2019-11-05 18:11:06 · 6227 阅读 · 0 评论 -
mysql sum() NULL 问题
今天在客户反馈线上数据出现了异常,如下图所示,正常值应该是百分百以内的,而且这个数值是随机出现的,刷新几下可能出现一次。然后和后端在检查了数据后,发现了select sum() 数据会随机出现不同的结果,而且是多次刷新后会出现的,再其次检查数据,发现了数据中有NULL值,然后参考了其他网友的分享,发现sum中,Number+null =null,求平均函数avg() 也会出现这样...原创 2019-11-05 15:03:00 · 3659 阅读 · 0 评论 -
mysql 取分组数据的前几名(1)
目前有需求需要取分组数据的前几名,有如下的解决方案来实现具体数据库如下SQL写法:SELECT * FROM student_grade AS a WHERE ( SELECT coun ( * ) FROM student_grade AS b WHERE b.subid = a.subid AND b.grade >= a.grade ) <= 2 O...原创 2019-11-01 16:47:27 · 4218 阅读 · 2 评论 -
取出mysql数据库中特定的表 REGEXP 正则表达式
由于需要从数据库取出特定名称的表名,需要用正则表达式来取出数据 ,尝试了几次,可以用 REGEXP 来实现,具体如下select table_name from information_schema.tables where table_schema='data_map' and (table_name REGEXP 'spl_201[0-5]' OR table_name REGEXP '...原创 2019-10-24 16:34:46 · 1603 阅读 · 0 评论 -
Mysql gis 空间数据库功能详解学习
下文为收集资料整理后,并测试后结果当前只有MyISAM引擎的数据表支持地理空间数据的存储建表:CREATEDATABASEgeodatabase;USEgeodatabase;CREATETABLEtest(idINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(128)NOTNULL,pntPOINT,...原创 2019-10-16 17:02:36 · 873 阅读 · 0 评论 -
先插入数据再建立索引与先建立索引再插入数据的区别
1、新建一个表结构,创建索引,将百万或千万级的数据使用insert导入该表。2、新建一个表结构,将百万或千万级的数据使用isnert导入该表,再创建索引。这两种效率哪个高呢?或者说用时短呢?表记录越大,索引个数越多,差异越明显。以前有过一个记录。某表记录有1亿条左右,12个索引,删除全部索引的插入速度和保留这12个索引的,插入速度百倍。其实原理很简单,边插入边维护索引,开销太大了。...原创 2019-09-29 15:17:49 · 9243 阅读 · 0 评论 -
MySQL字符串函数:字符串截取
MySQL 字符串截取函数:left(), right(), substring(), substring_index()。还有 mid(), substr()。其中,mid(), substr() 等价于 substring() 函数,substring() 的功能非常强大和灵活。1. 字符串截取:left(str, length)mysql> select left('sqls...转载 2019-04-24 16:12:17 · 249 阅读 · 0 评论 -
MySql拆分字符串(split拆分查找)
最近碰到了一个需求,判断当前登录用户是否有权限查看这行数据,数据如表A所示,如果当前用户具有A,D权限,那么他就能查到 列名为(1,2,3,5)这四条数据,因为要分页显示所以用Java去写显得很麻烦,所以用MySql的自定义函数解决了,代码如下所示:--------------------------------------------------------------邪恶的分割线-------...转载 2018-06-22 14:05:38 · 24573 阅读 · 0 评论