This is an old revision of the document!
option | description |
---|---|
noatime | |
barrier=0 | |
data=writeback |
shell> mysqldump --databases db1 db2 db3 > dump.sql
mysqldump --all-databases > dump.sql
You can query with the `@>` operator on `metadata`. This operator can compare partial JSON strings against a JSONB column. It’s the containment operator. For this case you may need to add a GIN index on `metadata` column.
SELECT * FROM users WHERE metadata @> '{"country": "Peru"}';
The `→>` operator gets a JSON object field as text. Use it if you want to query a simple field in a JSONB column. You might add a B-tree index on `metadata→>'country'`.
SELECT * FROM users WHERE metadata->>'country' = 'Peru';
Once again, the `→>` operator gets a JSON object field as text. Just use directly it in the `SELECT`.
SELECT metadata->>'country' FROM users;
You can use the `→>` operator with the classic operator you use on text: `=`,`<>`,`IS NULL`, etc. Do not forget to index `metadata→>'country'` with a B-tree index.
SELECT * FROM users WHERE metadata->>'country' IS NOT NULL;
You can use both `@>` or `→>`, just like for first level attribute. Add an index according to your choice.
SELECT * FROM users WHERE metadata->'company'->>'name' = "Mozilla"; SELECT * FROM users WHERE metadata @> '{"company":{"name": "Mozilla"}}';
Remembering `@>` operator checks containment in a JSONB column, you can query on an array like `{“x”: [“a”, “b”, “c”]“}` by just passing `{“x”:[“a”]}` to the `WHERE` clause:
SELECT * FROM users WHERE metadata @> '{"companies": ["Mozilla"]}';
Sometimes, we may need to select items where the attributes inside a JSONB column matches a bunch of possible values.
SELECT * FROM users WHERE metadata->>'countries' IN ('Chad', 'Japan');
Use `UPDATE … SET` as usual and pass the whole object as JSON.
UPDATE users SET metadata = '{"country": "India"}';
Use the `||` operator to concatenate the actual data with the new data. It will update or insert the value.
UPDATE users SET metadata = metadata || '{"country": "Egypt"}';
The operator - removes a key from an object.
UPDATE users SET metadata = metadata - 'country';
MariaDB [(none)]> show global variables like 'slow%log%'; +---------------------+-------------------------+ | Variable_name | Value | +---------------------+-------------------------+ | slow_query_log | ON | | slow_query_log_file | /var/log/mysql/slow.log | +---------------------+-------------------------+
MariaDB [(none)]> set global slow_query_log_file='/var/log/mysql/slow.log'; Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> set global slow_query_log=ON; Query OK, 0 rows affected (0.001 sec)