This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
public:it:database [2021/02/25 14:54] – [MariaDB] phil | public:it:database [Unknown date] (current) – removed - external edit (Unknown date) 127.0.0.1 | ||
---|---|---|---|
Line 1: | Line 1: | ||
- | ====== Database ====== | ||
- | |||
- | ===== Performance ===== | ||
- | ==== MariaDB ==== | ||
- | * [[https:// | ||
- | |||
- | === ext4 mount options === | ||
- | * [[https:// | ||
- | |||
- | ^option^description^ | ||
- | |noatime|access timestamps on the filesystem are not updated.| | ||
- | |commit=60|The sync interval for data and metadata can be increased by providing a higher time delay to the commit option.| | ||
- | |barrier=0|Careful! Read about this on the Arch wiki.| | ||
- | |||
- | |||
- | ===== Backup ===== | ||
- | |||
- | |||
- | ==== MYSQL ==== | ||
- | |||
- | < | ||
- | shell> mysqldump --databases db1 db2 db3 > dump.sql | ||
- | </ | ||
- | < | ||
- | mysqldump --all-databases > dump.sql | ||
- | </ | ||
- | ==== JSONB Cheatsheet ==== | ||
- | * [[https:// | ||
- | |||
- | |||
- | === Select items by the value of a first level attribute (#1 way) === | ||
- | 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 items by the value of a first level attribute (#2 way) === | ||
- | 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->>' | ||
- | |||
- | < | ||
- | |||
- | === Select item attribute value === | ||
- | Once again, the `->>` operator gets a JSON object field as text. Just use directly it in the `SELECT`. | ||
- | |||
- | < | ||
- | |||
- | === Select only items where a particular attribute is present === | ||
- | You can use the `->>` operator with the classic operator you use on text: `=`, | ||
- | |||
- | < | ||
- | |||
- | === Select items by the value of a nested attribute === | ||
- | You can use both `@>` or `->> | ||
- | |||
- | < | ||
- | SELECT * FROM users WHERE metadata->' | ||
- | </ | ||
- | |||
- | === Select items by the value of an attribute in an array === | ||
- | Remembering `@>` operator checks containment in a JSONB column, you can query on an array like `{" | ||
- | |||
- | < | ||
- | |||
- | === IN operator on attributes === | ||
- | Sometimes, we may need to select items where the attributes inside a JSONB column matches a bunch of possible values. | ||
- | |||
- | < | ||
- | |||
- | === Insert a whole object === | ||
- | Use `UPDATE ... SET` as usual and pass the whole object as JSON. | ||
- | |||
- | < | ||
- | |||
- | === Update or insert an attribute === | ||
- | Use the `||` operator to concatenate the actual data with the new data. It will update or insert the value. | ||
- | |||
- | < | ||
- | |||
- | === Removing an attribute === | ||
- | The operator - removes a key from an object. | ||
- | |||
- | < | ||
- | |||
- | |||
- | |||
- | ===== MYSQL/ | ||
- | |||
- | ==== Enable Logging ==== | ||
- | < | ||
- | MariaDB [(none)]> | ||
- | +---------------------+-------------------------+ | ||
- | | Variable_name | ||
- | +---------------------+-------------------------+ | ||
- | | slow_query_log | ||
- | | slow_query_log_file | / | ||
- | +---------------------+-------------------------+ | ||
- | </ | ||
- | |||
- | < | ||
- | MariaDB [(none)]> | ||
- | Query OK, 0 rows affected (0.000 sec) | ||
- | </ | ||
- | |||
- | < | ||
- | MariaDB [(none)]> | ||
- | Query OK, 0 rows affected (0.001 sec) | ||
- | </ | ||
- | |||
- | |||