MySQL json format data query operation

The default table name is base_data and the json column name is json_value

The value of json_value is

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

Then the query should be

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

or

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

Both are fine, the specific difference is still unknown

Supplementary knowledge: The problem of MySQL's varchar field being automatically truncated when the length exceeds the limit -----MySQL's sql_mode mode description and settings

Reasonable setting of MySQL sql_mode

sql_mode is a variable that is easily overlooked. The default value is null. Under this setting, some illegal operations can be allowed, such as allowing the insertion of some illegal data. In the production environment, this value must be set to strict mode, so the database of the development and test environment must also be set so that problems can be discovered during the development and testing phase.

SQL model is often used to solve the following types of problems

(1) By setting the sql mode, you can complete data verification with different degrees of strictness, effectively ensuring data readiness.

(2) By setting the SQL model to loose mode, we can ensure that most SQL statements conform to standard SQL syntax. This way, when migrating applications between different databases, we do not need to make major changes to the business SQL statements.

(3) Before migrating data between different databases, you can set the SQL Mode to make it easier to migrate data on MySQL to the target database.

Common values ​​for sql_mode are as follows:

ONLY_FULL_GROUP_BY:

For GROUP BY aggregation operations, if the columns in SELECT do not appear in GROUP BY, then this SQL is illegal because the columns are not in the GROUP BY clause.

NO_AUTO_VALUE_ON_ZERO:

This value affects inserts into auto-increment columns. By default, inserting 0 or NULL means generating the next auto-increment value. This option is useful if the user wants to insert a value of 0 and the column is auto-incrementing.

STRICT_TRANS_TABLES:

In this mode, if a value cannot be inserted into a transaction table, the current operation is interrupted, and no restrictions are imposed on non-transaction tables.

NO_ZERO_IN_DATE:

In strict mode, day and month values ​​of zero are not allowed.

NO_ZERO_DATE:

By setting this value, the MySQL database does not allow the insertion of zero date, and inserting zero date will throw an error instead of a warning.

ERROR_FOR_DIVISION_BY_ZERO:

During an INSERT or UPDATE, if data is divided by zero, an error is generated rather than a warning. If this mode is not given, MySQL returns NULL if the value is divided by zero.

NO_AUTO_CREATE_USER:

Prevent GRANT from creating users with empty passwords

NO_ENGINE_SUBSTITUTION:

If the required storage engine is disabled or not compiled in, an error is thrown. When this value is not set, the default storage engine is used instead and an exception is thrown.

PIPES_AS_CONCAT:

Treat "||" as a string concatenation operator rather than an OR operator, which is the same as Oracle database and is similar to the string concatenation function Concat.

ANSI_QUOTES:

When ANSI_QUOTES is enabled, you cannot use double quotes to quote strings, because they are interpreted as identifiers.

ORACLE's sql_mode setting is equivalent to:

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

If you use MySQL, in order to continue to keep everyone's habit of using Oracle, you can set MySQL's sql_mode as follows:

Add the following configuration in 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'

Notice:

The default sql_mode parameters of MySQL 5.6 and MySQL 5.7 are different. The mode of 5.6 is NO_ENGINE_SUBSTITUTION, which actually represents a null value, equivalent to no mode setting, which can be understood as a loose mode. The mode of 5.7 is STRICT_TRANS_TABLES, which is strict mode.

: : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : In addition to data verification, you will find that MySQL can do many more things as you learn it, covering many of the things you do in your program.

Problems that may occur after switching to strict mode:

If the setting mode contains NO_ZERO_DATE, the MySQL database does not allow the insertion of zero date, and inserting zero date will throw an error instead of a warning. For example, a TIMESTAMP column in a table (if not declared as NULL or with a DEFAULT clause) will automatically be assigned DEFAULT '0000-00-00 00:00:00' (zero timestamp), or the day column in the table of this test allows the insertion of zero date '0000-00-00' COMMENT 'date' by default; these obviously do not satisfy NO_ZERO_DATE in sql_mode and report an error.

Mode setting and modification (taking solving the above problem as an example):

Method 1:

First execute select @@sql_mode, copy the queried value and delete NO_ZERO_IN_DATE and NO_ZERO_DATE, then execute set sql_mode = 'modified value' or set session sql_mode = 'modified value';, for example: set session sql_mode = 'STRICT_TRANS_TABLES'; to change to strict mode

This method is only effective in the current session and will not take effect if the current session is closed.

Method 2:

First execute select @@global.sql_mode, copy the queried value and delete NO_ZERO_IN_DATE and NO_ZERO_DATE, then execute set global sql_mode = 'modified value'.

This method is effective in the current service and will become invalid after restarting the MySQL service.

Method 3: In the MySQL installation directory, or in the my.cnf file (my.ini file in Windows), add

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

Add my.cnf as follows:

[mysqld]

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER

Then restart mysql.

This method is effective permanently. Of course, restarting the MySQL service is prohibited in the production environment, so method 2 plus method 3 are used to solve online problems. Then, even if the MySQL service is really restarted one day, it will be effective permanently.

The above MySQL json format data query operation is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.