mysql json格式數(shù)據(jù)查詢操作

預(yù)設(shè)表名是 base_data , json列名是 json_value

json_value的值是

{“ids”:[“a”,“b”],“datas”:[{“name”:“張三”}]}

那么查詢語(yǔ)句應(yīng)該是

select * from base_data where JSON_CONTAINS(json_value -> "$.ids", JSON_ARRAY('a'));

或者

select * from base_data where JSON_CONTAINS(json_value -> "$.ids[*]", JSON_ARRAY('a'));

兩種都行，具體什么區(qū)別暫時(shí)還不得而知

補(bǔ)充知識(shí)：mysql中varchar字段長(zhǎng)度超過(guò)限制長(zhǎng)度自動(dòng)截取的問(wèn)題-----MySQL的sql_mode模式說(shuō)明及設(shè)置

MySQL的sql_mode合理設(shè)置

sql_mode是個(gè)很容易被忽視的變量,默認(rèn)值是空值,在這種設(shè)置下是可以允許一些非法操作的,比如允許一些非法數(shù)據(jù)的插入。在生產(chǎn)環(huán)境必須將這個(gè)值設(shè)置為嚴(yán)格模式,所以開發(fā)、測(cè)試環(huán)境的數(shù)據(jù)庫(kù)也必須要設(shè)置,這樣在開發(fā)測(cè)試階段就可以發(fā)現(xiàn)問(wèn)題.

sql model 常用來(lái)解決下面幾類問(wèn)題

(1) 通過(guò)設(shè)置sql mode, 可以完成不同嚴(yán)格程度的數(shù)據(jù)校驗(yàn)，有效地保障數(shù)據(jù)準(zhǔn)備性。

(2) 通過(guò)設(shè)置sql model 為寬松模式，來(lái)保證大多數(shù)sql符合標(biāo)準(zhǔn)的sql語(yǔ)法，這樣應(yīng)用在不同數(shù)據(jù)庫(kù)之間進(jìn)行遷移時(shí)，則不需要對(duì)業(yè)務(wù)sql 進(jìn)行較大的修改。

(3) 在不同數(shù)據(jù)庫(kù)之間進(jìn)行數(shù)據(jù)遷移之前，通過(guò)設(shè)置SQL Mode 可以使MySQL 上的數(shù)據(jù)更方便地遷移到目標(biāo)數(shù)據(jù)庫(kù)中。

sql_mode常用值如下:

ONLY_FULL_GROUP_BY:

對(duì)于GROUP BY聚合操作,如果在SELECT中的列,沒(méi)有在GROUP BY中出現(xiàn),那么這個(gè)SQL是不合法的,因?yàn)榱胁辉贕ROUP BY從句中

NO_AUTO_VALUE_ON_ZERO:

該值影響自增長(zhǎng)列的插入。默認(rèn)設(shè)置下,插入0或NULL代表生成下一個(gè)自增長(zhǎng)值。如果用戶 希望插入的值為0,而該列又是自增長(zhǎng)的,那么這個(gè)選項(xiàng)就有用了。

STRICT_TRANS_TABLES:

在該模式下,如果一個(gè)值不能插入到一個(gè)事務(wù)表中,則中斷當(dāng)前的操作,對(duì)非事務(wù)表不做限制

NO_ZERO_IN_DATE:

在嚴(yán)格模式下,不允許日期和月份為零

NO_ZERO_DATE:

設(shè)置該值,mysql數(shù)據(jù)庫(kù)不允許插入零日期,插入零日期會(huì)拋出錯(cuò)誤而不是警告。

ERROR_FOR_DIVISION_BY_ZERO:

在INSERT或UPDATE過(guò)程中,如果數(shù)據(jù)被零除,則產(chǎn)生錯(cuò)誤而非警告。如 果未給出該模式,那么數(shù)據(jù)被零除時(shí)MySQL返回NULL

NO_AUTO_CREATE_USER:

禁止GRANT創(chuàng)建密碼為空的用戶

NO_ENGINE_SUBSTITUTION:

如果需要的存儲(chǔ)引擎被禁用或未編譯,那么拋出錯(cuò)誤。不設(shè)置此值時(shí),用默認(rèn)的存儲(chǔ)引擎替代,并拋出一個(gè)異常

PIPES_AS_CONCAT:

將"||"視為字符串的連接操作符而非或運(yùn)算符,這和Oracle數(shù)據(jù)庫(kù)是一樣的,也和字符串的拼接函數(shù)Concat相類似

ANSI_QUOTES:

啟用ANSI_QUOTES后,不能用雙引號(hào)來(lái)引用字符串,因?yàn)樗唤忉尀樽R(shí)別符

ORACLE的sql_mode設(shè)置等同:

PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.

如果使用mysql,為了繼續(xù)保留大家使用oracle的習(xí)慣,可以對(duì)mysql的sql_mode設(shè)置如下:

在my.cnf添加如下配置

[mysqld]

sql_mode='ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,

ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,PIPES_AS_CONCAT,ANSI_QUOTES'

注意：

MySQL5.6和MySQL5.7默認(rèn)的sql_mode模式參數(shù)是不一樣的,5.6的mode是NO_ENGINE_SUBSTITUTION，其實(shí)表示的是一個(gè)空值，相當(dāng)于沒(méi)有什么模式設(shè)置，可以理解為寬松模式。5.7的mode是STRICT_TRANS_TABLES，也就是嚴(yán)格模式。

如果設(shè)置的是寬松模式，那么我們?cè)诓迦霐?shù)據(jù)的時(shí)候，即便是給了一個(gè)錯(cuò)誤的數(shù)據(jù)，也可能會(huì)被接受，并且不報(bào)錯(cuò)，例如：我在創(chuàng)建一個(gè)表時(shí)，該表中有一個(gè)字段為name，給name設(shè)置的字段類型時(shí)char(10)，如果我在插入數(shù)據(jù)的時(shí)候，其中name這個(gè)字段對(duì)應(yīng)的有一條數(shù)據(jù)的長(zhǎng)度超過(guò)了10，例如'1234567890abc'，超過(guò)了設(shè)定的字段長(zhǎng)度10，那么不會(huì)報(bào)錯(cuò)，并且取前十個(gè)字符存上，也就是說(shuō)你這個(gè)數(shù)據(jù)被存為了'1234567890',而'abc'就沒(méi)有了，但是我們知道，我們給的這條數(shù)據(jù)是錯(cuò)誤的，因?yàn)槌^(guò)了字段長(zhǎng)度，但是并沒(méi)有報(bào)錯(cuò)，并且mysql自行處理并接受了，這就是寬松模式的效果，其實(shí)在開發(fā)、測(cè)試、生產(chǎn)等環(huán)境中，我們應(yīng)該采用的是嚴(yán)格模式，出現(xiàn)這種錯(cuò)誤，應(yīng)該報(bào)錯(cuò)才對(duì)，所以MySQL5.7版本就將sql_mode默認(rèn)值改為了嚴(yán)格模式，并且我們即便是用的MySQL5.6，也應(yīng)該自行將其改為嚴(yán)格模式，而你記著，MySQL等等的這些數(shù)據(jù)庫(kù)，都是想把關(guān)于數(shù)據(jù)的所有操作都自己包攬下來(lái)，包括數(shù)據(jù)的校驗(yàn)，其實(shí)好多時(shí)候，我們應(yīng)該在自己開發(fā)的項(xiàng)目程序級(jí)別將這些校驗(yàn)給做了，雖然寫項(xiàng)目的時(shí)候麻煩了一些步驟，但是這樣做之后，我們?cè)谶M(jìn)行數(shù)據(jù)庫(kù)遷移或者在項(xiàng)目的遷移時(shí)，就會(huì)方便很多，這個(gè)看你們自行來(lái)衡量。mysql除了數(shù)據(jù)校驗(yàn)之外，你慢慢的學(xué)習(xí)過(guò)程中會(huì)發(fā)現(xiàn)，它能夠做的事情還有很多很多，將你程序中做的好多事情都包攬了。

改為嚴(yán)格模式后可能會(huì)存在的問(wèn)題：

若設(shè)置模式中包含了NO_ZERO_DATE，那么MySQL數(shù)據(jù)庫(kù)不允許插入零日期，插入零日期會(huì)拋出錯(cuò)誤而不是警告。例如表中含字段TIMESTAMP列（如果未聲明為NULL或顯示DEFAULT子句）將自動(dòng)分配DEFAULT '0000-00-00 00:00:00'（零時(shí)間戳），也或者是本測(cè)試的表day列默認(rèn)允許插入零日期 '0000-00-00' COMMENT '日期'；這些顯然是不滿足sql_mode中的NO_ZERO_DATE而報(bào)錯(cuò)。

模式設(shè)置和修改(以解決上述問(wèn)題為例)：

方式一：

先執(zhí)行select @@sql_mode,復(fù)制查詢出來(lái)的值并將其中的NO_ZERO_IN_DATE,NO_ZERO_DATE刪除，然后執(zhí)行set sql_mode = '修改后的值'或者set session sql_mode='修改后的值';，例如：set session sql_mode='STRICT_TRANS_TABLES';改為嚴(yán)格模式

此方法只在當(dāng)前會(huì)話中生效，關(guān)閉當(dāng)前會(huì)話就不生效了。

方式二：

先執(zhí)行select @@global.sql_mode,復(fù)制查詢出來(lái)的值并將其中的NO_ZERO_IN_DATE,NO_ZERO_DATE刪除，然后執(zhí)行set global sql_mode = '修改后的值'。

此方法在當(dāng)前服務(wù)中生效，重新MySQL服務(wù)后失效

方法三：在mysql的安裝目錄下，或my.cnf文件(windows系統(tǒng)是my.ini文件)，新增

sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION，

添加my.cnf如下：

[mysqld]

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER

然后重啟mysql。

此方法永久生效.當(dāng)然生產(chǎn)環(huán)境上是禁止重啟MySQL服務(wù)的，所以采用方式二加方式三來(lái)解決線上的問(wèn)題，那么即便是有一天真的重啟了MySQL服務(wù)，也會(huì)永久生效了。

以上這篇mysql json格式數(shù)據(jù)查詢操作就是小編分享給大家的全部?jī)?nèi)容了，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。