Category: Mysql

1、mysql查询条件自动去除左边的0

处理办法：字符串+0,可以自动转换

2、计算时间差

SELECT DATEDIFF(‘2008-8-21,’2009-3-21’); 跟SqlServer 差不多

3、 在Mysql 数据库中存在两种字符串连接操作.具体操作如下 ：

CONCAT(string1,string2,…) 说明 : string1,string2代表字符串,concat函数在连接字符串的时候，只要其中一个是NULL,那么将返回NULL 例1: 例2:

CONCAT_WS(separator,str1,str2,…) 说明 : string1,string2代表字符串,concat_ws 代表 concat with separator,第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。 举例1: mysql> select concat_ws(‘#’,’courseName=’,’NX’,null) AS nx_courseName;

MySQL中group_concat函数
完整的语法如下：
group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator ‘分隔符’])

基本查询

mysql> select * from stu1;
+——+——+
| id| name |
+——+——+
|1 | 10|
|1 | 20|
|1 | 20|
|2 | 20|
|3 | 200 |
|3 | 500 |
+——+——+
6 rows in set (0.00 sec)

以id分组，把name字段的值打印在一行，逗号分隔(默认)

mysql> select id,group_concat(name) from aa group by id;
+——+——————–+
| id| group_concat(name) |
+——+——————–+
|1 | 10,20,20|
|2 | 20 |
|3 | 200,500|
+——+——————–+
3 rows in set (0.00 sec)

以id分组，把name字段的值打印在一行，分号分隔

mysql> select id,group_concat(name separator ‘;’) from aa group by id;
+——+———————————-+
| id| group_concat(name separator ‘;’) |
+——+———————————-+
|1 | 10;20;20 |
|2 | 20|
|3 | 200;500 |
+——+———————————-+
3 rows in set (0.00 sec)

以id分组，把去冗余的name字段的值打印在一行，

逗号分隔

mysql> select id,group_concat(distinct name) from aa group by id;
+——+—————————–+
| id| group_concat(distinct name) |
+——+—————————–+
|1 | 10,20|
|2 | 20 |
|3 | 200,500 |
+——+—————————–+
3 rows in set (0.00 sec)

以id分组，把name字段的值打印在一行，逗号分隔，以name排倒序

mysql> select id,group_concat(name order by name desc) from aa group by id;
+——+—————————————+
| id| group_concat(name order by name desc) |
+——+—————————————+
|1 | 20,20,10 |
|2 | 20|
|3 | 500,200|
+——+—————————————+
3 rows in set (0.00 sec)

还有一个简单的连接方式为: ||

mysql> select * from student;
+—-+——+——-+———-+————+
| id | age | score | name | birth |
+—-+——+——-+———-+————+
| 1 | 23 | 78 | 李四 | 2017-10-10 |
| 2 | 24 | 78 | zhangsan | 2017-10-10 |
| 3 | 25 | 99 | 王五 | 2016-05-17 |
+—-+——+——-+———-+————+
3 rows in set (0.00 sec)

mysql> select id+999,name,name+99,name+’999′ from student;
+——–+———-+———+————+
| id+999 | name | name+99 | name+’999′ |
+——–+———-+———+————+
| 1000 | 李四 | 99 | 999 |
| 1001 | zhangsan | 99 | 999 |
| 1002 | 王五 | 99 | 999 |
+——–+———-+———+————+
3 rows in set, 6 warnings (0.00 sec)

模糊查询

Mysql实现模糊查询 最简单的是LIKE关键字, 如

 SELECT * FROM `content` WHERE `topic` LIKE '%地球%';

而当然也可以使用LOCATE(),POSITION()等内置函数来实现. 不过 这种模糊查询都存在一定的局限性. 举个🌰:

记录为: 你好,我的世界, 此时通过关键词你好世界 便无法搜索到.

如何解决

在Mysql 5.7.6后 Mysql内置了ngram分词疫情, 可以实现中文, 日文, 韩文的解析. 我们需要对指定字段建立全文索引并指定分词引擎.

• 需要注意: 建立全文索引的字段 数据类型只能为 VARCHAR, TEXT, CHAR

设置分词

我们需要先设置ngram的分词长度, 由于中文词语一般为两个字, 所以建议设置为2

mysqld --ngram_token_size=2  

也可以通过修改mysql配置文件, 指定ngram_token_size=2

建立索引

选定分词引擎建立FLULTEXT索引

ALTER TABLE `table_name` ADD FULLTEXT INDEX `index_name`(`column_name`) WITH PARSER ngram;

进行搜索

建立索引后, 可以通过 match against语句进行搜索

SELECT * FROM `table` WHERE MATCH (`column`) against('你好世界')

全文索引的两种搜索模式

• 自然语言搜索(也是默认的搜索模式): 不能使用操作符 进行复杂检索.
• BOOLEAN模式 : 可以通过操作符 进行复杂搜索, 与搜索引擎类似.

SELECT * FROM `table` WHERE MATCH (`column`) against('你好世界' IN NATURAL LANGUAGE MODE)

# 必须包含'你好', 但不能包含'我的'
SELECT * FROM `table` WHERE MATCH (`column`) against('+你好 -我的' IN BOOLEAN MODE)

参考文章

• MySql相似度搜索

实现查询出艾宾浩斯记忆规律时间点的数据

SELECT id,from_unixtime(pubdate) FROM sq_question WHERE TO_DAYS( NOW( ) ) - TO_DAYS(from_unixtime(pubdate)) IN (1,2,4,7,15,30);

今天

select * from 表名 where to_days(时间字段名) = to_days(now());

昨天

SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1

近7天

SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)

近30天

SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)

本月

SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )

上一月

SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1

查询本季度数据

select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());

查询上季度数据

select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

查询本年数据

select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());

查询上年数据

select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));

查询当前这周的数据

SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());

查询上周的数据

SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;

查询上个月的数据

select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m') select * from user where DATE_FORMAT(pudate,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') ;  select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(now())  select * from user where MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now())  select * from user where YEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = YEAR(now()) and MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now())  select * from user where pudate between  上月最后一天  and 下月第一天 

查询当前月份的数据 

select name,submittime from enterprise   where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')

查询距离当前现在6个月的数据

select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();