MySQL 字符串与时间操作函数
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://www.cnblogs.com/LyShark/p/13225752.html
本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
