mysql 日期增加索引有效果吗

在日常业务开发函数中,除了常用的crud ,有时候调整、查询数据还需要用到常用、常见的函数,使用这些函数的原因除了进行业务调整以外,还是对操作的数据,进行记录

2. GROUP_CONCAT



3. 比如需要将查询结果的ID进行其他业务处理,而这个sql又比较复杂且结果不直观时候, 我们可以将查询结果的ID进行返回然后保存当前做他sql的参数
   比如
   select GROUP_CONCAT(distinct id) from user_info
   – 结果 1,2,3,4,5,6,7,9,10,11,12

– 将上面的sql 运行后得到结果 再进行拼接 单引号 ’ 内容值 ’ 就可以直接放到下面的一个函数 FIND_IN_SET 当作参数内容值
注意 如果数据量特别多的时候,需要手动执行 一条sql 或者修改配置文件 设置临时的数据拼接最大长度. 比如有几千个返回结果值时

-- 查询 查询group_concat 函数的最大返回值长度
show variables like 'group_concat_max_len'

-- 查询结果  变量名 variables         值(value)
--          group_concat_max_len	1024

方式一:修改配置文件my.ini
group_concat_max_len = 102400

方式二:执行命令语句
-- 设置全局的最大长度 只需执行一次
SET GLOBAL group_concat_max_len = 102400;
-- 每次开启新的查询都需要执行的命令
SET SESSION group_concat_max_len = 102400;

2. FIND_IN_SET

select * from user_info where  FIND_IN_SET(id,'1,2,3,4,5,6,7,9,10,11,12');

此函数和上面的GROUP_CONCAT 函数 搭配使用 效果非常好

这样做的好处, 不用将上一步查询列结果 手动拼接成 ‘num1’,‘num2’ 再做为 in 查询条件 的参数值 .而是直接可以使用

如果需要将查询结果列 拼接为 查询条件为 in 的sql语句中 那么可以使用如下的2种方式将结果拼接

1. 使用wps的 textjoin函数

假设数据结果列有几千条.这里只是一点数据做为示例

2 .还是使用 GROUP_CONCAT函数 但是将最后的分割符号改变为 " ‘,’ "

注意 此函数一般做为 直接手动操作数据库中的数据

这两种方法都可以实现拼接数据的效果,但是 需要手动在结果的前后都加一个单引号 . 最后这种函数在业务实现的代码里面 运行效果肯定是很慢的,比如数据量是1000万,那么这个函数要执行1000 万次.

3. 日期函数
   3.1 获取一个日期是星期几 DAYOFWEEK(‘yyyy-MM-dd’) 或者 DAYOFWEEK(‘yyyy-MM-dd HH:mm:ss’)

select DAYOFWEEK('2022-06-02 14:23:33');
-- 结果5 
select DAYOFWEEK('2022-06-02');
-- 结果5

注意这里返回的 是从星期天开始计数 为1 星期六 计数为7

3.2 添加日期 及日期格式的转换

3.3 str_to_date(str,format)

select str_to_date('2022-06-02','%Y-%m-%d');
-- 结果 2022-06-02
select DAYOFWEEK('2022-06-02');
-- 结果5

在实际业务中可能会出现 传递的时间参数有误 如:

-- 只是尽量不要用这种方式,因为数据量过大的时候,每一条数据都会被执行一次这个函数
select * from user_info where user_info.birthday >= STR_TO_DATE('2-8 2022','%c-%d %Y')

3.4 获取当前时间

select CURRENT_DATE();
-- 2022-06-07
select CURRENT_TIME();
-- 13:28:51
select CURRENT_TIMESTAMP();
-- 2022-06-07 13:28:51

3.5 DATEDIFF 获取两个时间的时间差

5. 字符串长度函数 length(‘str’)



6. 因为 中文字是utf-8的格式所以一个中文字占3个字符. 要想显示正常,只需要将格式还原设置为 utf-8 就可以, 比如命令 set character_set_client=‘utf8’ .也可以在配置文件中进行详细.
8. 函数 CONCAT(str,str.str····)
   用此函数可以将返回的列内容进行拼接 ,并且可以自定义拼接的规格及间隔内容.非常方便



10. 函数 UPPER(str),LOWER(str)
    将结果转换大小写



12. 函数 SUBSTR()



14. INSTR(str1,str2)函数



15. TRIM(str1 from str2)函数

select LENGTH(TRIM('     sdf   '));
--  计算的是去掉空格后的长度 3

-- 指定重复字符  字符中间的数据不能删除
select TRIM('c' FROM 'ccccccccc12c3c4c5c6ccccccc');
select TRIM('abc' FROM 'abcabcabc12c3c4c5c6abcabcabc');

10. TRIM(str1 from str2)函数

select LENGTH(TRIM('     sdf   '));
--  计算的是去掉空格后的长度

11. lpad(str1,num,str2)函数
    向左 按照str2 函数长度的值来填充数据
    rpad(str1,num,str2)
    向右 按照str2 函数长度的值来填充数据 同上,只是增长的方向不一样
12. lpad(str1,num,str2)函数

-- 原数据 被替换的数据 要替换的内容 
-- 12345GGGGGE
select REPLACE('12345ABCDE','ABCD','GGGGG');

13. 数学函数

-- 向上取整
select CEIL(10.1);
-- 向下取整
select FLOOR(10.9);

-- 截断到小数点后的多少位
select TRUNCATE(1.81231231,5);

-- 取余数 其实就是 a- a/b * b 虽然 先乘再除 
-- 但是 a/b 有可能为 不能整除的数这时候只取除得得整数 
-- 比如 11231.13 - 11231.13 / 2 *2
select MOD(11231.13,2);

14. case 和 when 函数 同时when 后面可以跟逻辑判断语句

select id,sex, 
case sex 
when 1 then '男'
when 2 then '女'
else 0
END as 性别
 from user_info;


-- 组合update函数 假设业务场景如下
 update user_info info1
  INNER JOIN (select id,sex, 
case sex 
when 1 then '男'
when 2 then '女'
else 0
END as sex2
 from user_info) info2 on info1.id=info2.id
 set info1.sex=info2.sex2

15. if 函数

if 函数 可以嵌套  
select id, sex , if (sex ='1' ,'男',(IF(sex is null or sex='','','女'))) as sex2 from user_info;
-- 结果
-- 1	1	男
-- 2		
-- 3	2	女

16. sum、avg、count、 min 、max等函数值都是忽略了null值进行计算的
    sum函数和avg函数 的计算规则,当值为null时是不会被携带进行计算的 ,如 总数是100 ,其中30个数据是为null,70 个数据是有值的数据,那么avg函数计算的结果就是 sum(70个有值的列) / 70
    同理 如果 count(id) 和count(name) 两个结果不一致,说明 name字段存在null 的值 .
17. count(*)、count(列)、count(1)的区别
    在业务代码中常使用的这些count到底有什么区别 ?

• count (*) 在表中数据的每一行 只要有一个字段不为空,就增加一个计数



• count(1). 自定义维护一个 值为1的 列名和值内容 都为1 的虚拟列,所以这个值内容可以自定义 ,因为这个都是添加的虚拟列



• count(列) 单独去查询这一列的内容值 上面提到,为null的内容值是不计数的



• 以上情况的描述 在有无where条件过滤情况下一样,如果有条件过滤,那么就是扫描过滤后的列 如果没有条件过滤就扫描全表.
• 查询效率
• 在MYISAM 数据库中 count(*) 效率最高
• 在INNODB 中 count(*)和count(1)的效率是差不多的 count(列)的效率最低,因为他还包含了 列的值是否为null的一步逻辑在里面所以效率就低了

18. 分组函数查询的记忆技巧

select 分组函数, 查询列(跟在group by 后面的字段)
from 表名
where 查询条件
group by 列 
having 分组函数

17. IP地址存储在数据库的方式 inet_aton()、inet_ntoa() 在很多面试题或者文档中都已经明确表示过理由 ,ip地址实际上是一个整形数字,应该用int类型的值来存储,那么 这两个函数就是用来转换ip地址类型的函数
    ipv4 int 类型的长度定为最长10位数就足够了,因为 最大的ipv4 的int转换值也才10位数
    ipv6 则需要32位长度的char类型或者varchar类型

大数据量时的优化

当一张未做任何优化的业务表数据达到百万级别的时候,分页查询的效率越往后越慢,尤其是最后的几页数据的查询效率甚至是需要几十秒的时间,这个肯定是不允许的.

1. 分析查询效率慢的原因
   假设现在有一张表数据量到达100万以上. 且无任何索引 ,使用传统的 分页查询 及 count(*) 查询统计总条数 需要进行两次sql查询才能得到结果 .
2. 解决方法 在业务使用中了解到一个SQL_CALC_FOUND_ROWS 关键字和 FOUND_ROWS() 函数方法, FOUND_ROWS() 的作用是获取记录的 上一次select 语句的总行数.如果在select 语句中不添加关键字 SQL_CALC_FOUND_ROWS那么 下面的FOUND_ROWS() 函数返回的是select分页函数的最后一条数据的行数.比如

select * from User limit 10000,10; select FOUND_ROWS() as count;
-- 返回的结果是 ID 从 10001 到 10010 的数据,而FOUND_ROWS 函数返回的值是 10010

使用SQL_CALC_FOUND_ROWS 函数的返回如下

select SQL_CALC_FOUND_ROWS * from User limit 10000,10; select FOUND_ROWS() as count;
-- 结果是 ID 从 10001 到 10010 的数据,而FOUND_ROWS 函数返回的值是 1000000

在代码中的使用方法

ServiceImpl

@Override
    @SuppressWarnings("unchecked")
    public Page<User> queryPage(int pageNo, int pageSize,String userName) {
        Page<User> page = new Page<>(pageNo,pageSize);
        // 根据需求自定义查询条件
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.lambda().like(StringUtils.isNotEmpty(userName),User::getNickname,userName);
        // 调用MyBatis-plus中自定义的类 
        List list= this.baseMapper.queryPage(queryWrapper,(pageNo-1)*pageSize,pageSize);
        if (!CollectionUtils.isEmpty(list)) {
            List<User> users = (List<User>) list.get(0);
            List<Integer> integers = (List<Integer>) list.get(1);
            page.setRecords(users);
            page.setTotal(integers.get(0));
        }
        return page;
    }

在Mapper中的方法定义

/**
     * 查询list
     * @param pageNo
     * @param pageSize
     * @return
     */
    List queryPage(@Param(Constants.WRAPPER) QueryWrapper<User> queryWrapper,@Param("pageNo")int pageNo,@Param("pageSize")int pageSize);

在xml文件中的定义

<!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="user类的路径">
        <id column="id" property="id" />
       <!-- 类的属性和数据库的字段对应 -->
    </resultMap>

  <resultMap id="counts" type="java.lang.Integer">
        <result column="count" javaType="Integer" jdbcType="INTEGER"></result>
        
    </resultMap>
    <select id="queryPage" resultMap="BaseResultMap,counts">
        select SQL_CALC_FOUND_ROWS * from User ${ew.customSqlSegment}
         limit #{pageNo},#{pageSize};
        select FOUND_ROWS() as count;
    </select>

至此 还是不能生效 ,因为还需要在数据库url连接中添加一个配置 allowMultiQueries=true ,让代码中的sql可以批量执行多个查询sql

url: jdbc:mysql://ip:端口/数据库名称?userUnicode=true&allowMultiQueries=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai

此时就可以直接使用一次分页查询,获取到返回的数据结果及总条数了.