分类 Mysql 下的文章

一篇文章标题有时候会有空格存入数据库,在搜索的时候需要对空格进行过滤,
使用 mysqlTRIM()REPLACE()函数对字符串处理。

SELECT title FROM article WHERE TRIM(REPLACE(`title`,' ','')) = ?

首尾去空格可以在后端代码中存储过程实现.

多字段搜索 CONCAT

CONCAT(str1,str2,...)

返回由全体参数合并在一起而得到的字符串,只要有一个参数为NULL那就返回NULL

在Mysql中存储具有一些格式的数据的时候使用JSON格式存储会很方便,Mysql 5.7.8 开始支持存储原生JOSN格式的数据,MariaDB 10.0.1版本优化了JOSN格式的处理。

两者还是有区别的,在此记录下如何对JSON字段的CURD

创建JSON格式字段

创建JOSN字段不能设置长度,和默认值,可以是NULL

--MYSQL
CREATE TABLE happyhack(
 `id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
 `area` JSON,
 `tags` JSON
);
--Mariadb
CREATE TABLE happyhack(
`area` BLOB
);

MariaDB [test]> DESC happyhack;
--------------
DESC happyhack
--------------

+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| area  | longtext         | YES  |     | NULL    |                |
| tags  | longtext         | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+

插入JSON数据

INSERT INTO `happyhack` (area,tags) VALUES ('{"id": 1, "name": "如皋"}', '[1, 2, 3]');

Mysql 也有两个函数用来插入Json格式的数据JSON_OBJECTJSON_ARRAY
MariaDB 使用COLUMN_CREATE

INSERT INTO `happyhack` (area, tags) VALUES (JSON_OBJECT("id", 2, "name", "江苏"), JSON_ARRAY(1, 3, 5));

-- 这个在MariaDB中没有成功..
INSERT INTO `happyhack` (area) VALUES(COLUMN_CREATE('id','1','name','中国'));
+----+-----------------------------+-----------+
| id | area                        | tags      |
+----+-----------------------------+-----------+
|  1 | {"id": 1, "name": "如皋"}   | [1, 2, 3] |
|  2 | {"id": 2, "name": "江苏"}   | [1, 3, 5] |
+----+-----------------------------+-----------+

查询JSON格式字段

查询 JSON 中的数据用 column->path 的形式,其中对象类型 path 这样表示 $.path, 而数组类型则是 $[index]

MariaDB 貌似不支持这种写法,使用JSON_EXTRACT函数可以实现

MariaDB [test]> SELECT area, JSON_EXTRACT(area,'$.id'),JSON_EXTRACT(tags,'$[0]') FROM happyhack;
--------------
SELECT area, JSON_EXTRACT(area,'$.id'),JSON_EXTRACT(tags,'$[0]') FROM happyhack
--------------

+-----------------------------+---------------------------+---------------------------+
| area                        | JSON_EXTRACT(area,'$.id') | JSON_EXTRACT(tags,'$[0]') |
+-----------------------------+---------------------------+---------------------------+
| {"id": 1, "name": "如皋"}   | 1                         | 1                         |
| {"id": 2, "name": "江苏"}   | 2                         | 1                         |
+-----------------------------+---------------------------+---------------------------+

--MariaDB
SELECT area, JSON_EXTRACT(area,'$.name'),JSON_EXTRACT(tags,'$[0]') FROM happyhack;
--Mysql
SELECT area,area->'$.name',tags->'$[0]' from happyhack;

取出来的对象数据会自带"号,可以使用JSON_UNQUOTE函数去掉,目前没有Mysql数据库,下次遇到在测试

按条件查询JSON字段数据

JSON格式的数据不同于字符串,使用字符串相等查询是查询不到的;
可以使用CAST函数将字符串转成JSON格式的查询。

SELECT area FROM happyhack WHERE area = CAST('{"id": 1, "name": "如皋"}' as JSON);

SELECT area FROM happyhack WHERE area->'$.name' ='如皋";
--以上两种方法没有在Mysql中测试,以后遇到会记录

使用JSON_EXTRACT查询

SELECT area FROM happyhack WHERE JSON_EXTRACT(area,'$.name') = '如皋';

PHP中将JSON存入DB注意点

使用json_encode方法转为json存数据库的时候,如果是中文字符会被转义成别的码...使用JSON_UNESCAPED_UNICODE就好啦!

json_encode($arr,JSON_UNESCAPED_UNICODE);

Laravel 中查询JSON

Client::whereRaw('JSON_EXTRACT(client_region, "$[0]") like ?', ["%$request->info%"])->get();

未完待续!

参考链接

http://www.lnmp.cn/mysql-57-new-features-json.html

https://www.omgdba.com/using-json-datatype-in-mariadb-and-mysql.html