♥ 文章声明 ♥该系列文章部分文字描述,参考于以下文献,化繁为简.，接下来我们就来聊聊关于mysql中日期函数?以下内容大家不妨参考一二希望能帮到您!

mysql中日期函数（字符串与时间操作函数）

mysql中日期函数

♥ 文章声明 ♥

该系列文章部分文字描述,参考于以下文献,化繁为简.

《MySQL5.7从入门到精通》 - 刘增杰

MariaDB [lyshark]> select Name,char_length(Name) from lyshark; -- 求字符串长度 ------------ ------------------- | Name | char_length(Name) | ------------ ------------------- | apple | 5 | | apricot | 7 | | blackberry | 10 | ------------ ------------------- 17 rows in set (0.00 sec) MariaDB [lyshark]> select concat("{ID: ",Gid,", Name: ",Name,"}") from lyshark; ----------------------------------------- | concat("{ID: ",Gid,", Name: ",Name,"}") | ----------------------------------------- | {ID: 101, Name: apple} | | {ID: 103, Name: apricot} | | {ID: 101, Name: blackberry} | | {ID: 104, Name: berry} | ----------------------------------------- 17 rows in set (0.00 sec) -- 如果为空则输出null MariaDB [lyshark]> select concat("{ID: ",Gid,", Name: ",ifnull(Name,"NULL"),"}") from lyshark; -- 以，作为连接，将两个值串起来。 MariaDB [lyshark]> select concat_ws(',',"Name: ","lyshark"); ----------------------------------- | concat_ws(',',"Name: ","lyshark") | ----------------------------------- | Name: ,lyshark | ----------------------------------- 1 row in set (0.00 sec) -- 字符串替换，从第7个字符串开始替换，向后替换10个。 MariaDB [lyshark]> select insert('hello world',7,10,'lyshark'); -------------------------------------- | insert('hello world',7,10,'lyshark') | -------------------------------------- | hello lyshark | -------------------------------------- 1 row in set (0.00 sec) -- 返回str字符串中第一个出现substr字符串的位置 MariaDB [lyshark]> select instr('hello lyshark','lyshark'); ---------------------------------- | instr('hello lyshark','lyshark') | ---------------------------------- | 7 | ---------------------------------- 1 row in set (0.00 sec) -- 字符串截断 MariaDB [lyshark]> select left('hello lyshark',3); ------------------------- | left('hello lyshark',3) | ------------------------- | hel | ------------------------- 1 row in set (0.00 sec) MariaDB [lyshark]> select right('hello lyshark',3); -------------------------- | right('hello lyshark',3) | -------------------------- | ark | -------------------------- 1 row in set (0.00 sec) -- 返回str字符串的byte字节长度 MariaDB [lyshark]> select length('aaaaafasdfasd'); ------------------------- | length('aaaaafasdfasd') | ------------------------- | 13 | ------------------------- 1 row in set (0.00 sec) -- 返回str字符串中第一次出现substr字符串的位置，如果没有则返回null MariaDB [lyshark]> select locate('lys','hello lyshark'); ------------------------------- | locate('lys','hello lyshark') | ------------------------------- | 7 | ------------------------------- 1 row in set (0.00 sec) MariaDB [lyshark]> select locate('bar','foobarbar',5); ----------------------------- | locate('bar','foobarbar',5) | ----------------------------- | 7 | ----------------------------- 1 row in set (0.00 sec) -- 字符串大小写转换 MariaDB [lyshark]> select lower('ABD'); -------------- | lower('ABD') | -------------- | abd | -------------- 1 row in set (0.00 sec) MariaDB [lyshark]> select upper('abc'); -------------- | upper('abc') | -------------- | ABC | -------------- 1 row in set (0.00 sec) -- 去掉左右两边的空格 MariaDB [lyshark]> select ltrim(' abc dde eee '); ----------------------------- | ltrim(' abc dde eee ') | ----------------------------- | abc dde eee | ----------------------------- 1 row in set (0.00 sec) MariaDB [lyshark]> select rtrim(' abc dde eee '); ----------------------------- | rtrim(' abc dde eee ') | ----------------------------- | abc dde eee | ----------------------------- 1 row in set (0.00 sec) MariaDB [lyshark]> select trim(' abc dde eee '); ---------------------------- | trim(' abc dde eee ') | ---------------------------- | abc dde eee | ---------------------------- 1 row in set (0.00 sec)

-- 替换字符串中的字符 MariaDB [lyshark]> select replace('hello world world','world','lyshark'); ------------------------------------------------ | replace('hello world world','world','lyshark') | ------------------------------------------------ | hello lyshark lyshark | ------------------------------------------------ 1 row in set (0.00 sec) MariaDB [lyshark]> select substring('sublyshark',3,2); ----------------------------- | substring('sublyshark',3,2) | ----------------------------- | bl | ----------------------------- 1 row in set (0.00 sec) -- 字符串截取，联系 MariaDB [lyshark]> insert into if_test(name,sex) values("zhang san",1); MariaDB [lyshark]> insert into if_test(name,sex) values("li si",1); MariaDB [lyshark]> insert into if_test(name,sex) values("wang wu",1); MariaDB [lyshark]> select name,locate(' ',name) from if_test; ----------- ------------------ | name | locate(' ',name) | ----------- ------------------ | lyshark | 0 | | admin | 0 | | luxi | 0 | | zhang san | 6 | | li si | 3 | | wang wu | 5 | ----------- ------------------ 6 rows in set (0.00 sec) MariaDB [lyshark]> select name,substring(name,locate(' ',name)) from if_test; ----------- ---------------------------------- | name | substring(name,locate(' ',name)) | ----------- ---------------------------------- | zhang san | san | | li si | si | | wang wu | wu | ----------- ---------------------------------- 6 rows in set (0.00 sec) MariaDB [lyshark]> select name,substring(name,1,locate(' ',name) 1) from if_test; ----------- ------------------------------------ | name | substring(name,1,locate(' ',name)) | ----------- ------------------------------------ | zhang san | zhang | | li si | li | | wang wu | wang | ----------- ------------------------------------ 6 rows in set (0.00 sec)

MariaDB [lyshark]> select * from lyshark order by rand(); -- 随机输出数据 MariaDB [lyshark]> select floor(rand()*10); -- 取出随机整数 MariaDB [lyshark]> select round(10.25); -- 时区转换 MariaDB [lyshark]> select CONVERT_TZ('2009-12-11 12:00:00',' 00:00',' 10:00'); ----------------------------------------------------- | CONVERT_TZ('2009-12-11 12:00:00',' 00:00',' 10:00') | ----------------------------------------------------- | 2009-12-11 22:00:00 | ----------------------------------------------------- 1 row in set (0.00 sec) MariaDB [lyshark]> select curdate(); ------------ | curdate() | ------------ | 2020-07-02 | ------------ 1 row in set (0.00 sec) MariaDB [lyshark]> select current_date(); ---------------- | current_date() | ---------------- | 2020-07-02 | ---------------- 1 row in set (0.00 sec) MariaDB [lyshark]> select now(); --------------------- | now() | --------------------- | 2020-07-02 07:30:26 | --------------------- 1 row in set (0.00 sec) MariaDB [lyshark]> select sysdate(); --------------------- | sysdate() | --------------------- | 2020-07-02 07:30:54 | --------------------- 1 row in set (0.00 sec) MariaDB [lyshark]> select current_time(); ---------------- | current_time() | ---------------- | 07:31:12 | ---------------- 1 row in set (0.00 sec) -- 获取差异时间 MariaDB [lyshark]> select datediff('2020-12-25','2020-11-22'); ------------------------------------- | datediff('2020-12-25','2020-11-22') | ------------------------------------- | 33 | ------------------------------------- 1 row in set (0.00 sec) -- 时间增加减少 MariaDB [lyshark]> select date_add('2020-11-24',INTERVAL -100 SECOND); // 减少100秒 MariaDB [lyshark]> select date_add('2020-11-24',INTERVAL 100 SECOND); // 增加100秒 MariaDB [lyshark]> select date_add('2020-11-24',INTERVAL '12:22' MINUTE_SECOND); ------------------------------------------------------- | date_add('2020-11-24',INTERVAL '12:22' MINUTE_SECOND) | ------------------------------------------------------- | 2020-11-24 00:12:22 | ------------------------------------------------------- 1 row in set (0.00 sec) SECOND/DAY/WEEK/HOUR/ -- 日期格式转换 MariaDB [lyshark]> select date_format(now(),'%Y:%m:%d'); ------------------------------- | date_format(now(),'%Y:%m:%d') | ------------------------------- | 2020:07:02 | ------------------------------- 1 row in set (0.00 sec) MariaDB [lyshark]> select dayofmonth('2020-06-24'); 返回date中当前月份是第几天 MariaDB [lyshark]> select dayname('2020-06-24'); 返回date中是星期几 MariaDB [lyshark]> select dayofweek('2020-06-24'); 返回date时间是星期几 MariaDB [lyshark]> select extract(YEAR from '2009-12-21'); 获取年份 MariaDB [lyshark]> select extract(YEAR_MONTH from '2009-12-21'); 获取年月 MariaDB [lyshark]> select last_day('2015-12-12'); 获取当月最后一天 MariaDB [lyshark]> select unix_timestamp(); unix时间戳 MariaDB [lyshark]> select from_unixtime(unix_timestamp()); 将时间戳转换为时间

字符集

1.设置my.ini

[mysqld] # 服务端使用的字符集默认为UTF8 character-set-server=utf8 [client] # 设置mysql客户端连接服务端时默认使用的端口 default-character-set=utf8 MariaDB [lyshark]> select * from information_schema.character_sets; 查询所支持的字符集 MariaDB [lyshark]> show character set like 'utf8%'; MariaDB [lyshark]> show variables like 'character_set%'; set global character_set_client=utf8; set global character_set_connection=utf8; set global character_set_database=utf8; set global character_set_results=utf8; set global character_set_server=utf8; MariaDB [lyshark]> alter database lyshark default character set utf8 collate utf8_general_ci; MariaDB [lyshark]> alter table lyshark.user convert to character set utf8 collate utf8_general_ci;

表分区

表分区是将⼀个表的数据按照⼀定的规则⽔平划分为不同的逻辑块，并分别进⾏物理存储，

这个规则就叫做分区函数

DROP TABLE IF EXISTS `emept`; create table emept ( u_id int not null, u_name varchar(30), store_id int not null ) PARTITION BY RANGE(store_id) (PARTITION p0 values less than(6),PARTITION p1 values less than(11), PARTITION p2 values less than(16),PARTITION p3 values less than(21) ); insert into emept values(1,'aaaa',1); insert into emept values(2,'bbbb',2); insert into emept values(3,'aaaa',3); insert into emept values(3,'aaaa',8); insert into emept values(3,'aaaa',19); explain select * from emept where store_id=1; MariaDB [lyshark]> create table abc(name varchar(20)) charset utf8mb4;

来源：https://blog.51cto.com/lyshark/5322127

“做程序员，圈子和学习最重要”因为有有了圈子可以让你少走弯路，扩宽人脉，扩展思路，学习他人的一些经验及学习方法！同时在这分享一下是一直以来整理的Java后端进阶笔记文档和学习资料免费分享给大家！需要资料的朋友私信我扣【06】

,