① 创建表，插入测试记录

mysql> CREATE TABLE my_json (j1 JSON, j2 JSON);

mysql> INSERT INTO my_json VALUES

-> ('{"k1": "value", "k2": 10}', '["run", "sing"]');

② 查询记录

mysql> SELECT * FROM my_json;

+---------------------------+-----------------+

| j1 | j2 |

+---------------------------+-----------------+

| {"k1": "value", "k2": 10} | ["run", "sing"] |

+---------------------------+-----------------+

1 row in set (0.00 sec)

默认约束使用示例

mysql> CREATE TABLE my_default (

-> name VARCHAR(10),

-> age INT UNSIGNED DEFAULT 18

-> );

查看表结构

mysql> DESC my_default;

+-------+------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+------------------+------+-----+---------+-------+

| name | varchar(10) | YES | | NULL | |

| age | int(10) unsigned | YES | | 18 | |

+-------+------------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

插入记录进行测试

① 在插入记录时省略 name 和 age 字段

mysql> INSERT INTO my_default VALUES();

Query OK, 1 row affected (0.00 sec)

② 在插入记录时省略 age 字段

mysql> INSERT INTO my_default (name) VALUES('a');

Query OK, 1 row affected (0.00 sec)

③ 在 age 字段中插入 NULL 值

mysql> INSERT INTO my_default VALUES('b', NULL);

Query OK, 1 row affected (0.00 sec)

④ 在 age 字段中使用默认值

mysql> INSERT INTO my_default VALUES('c', DEFAULT);

Query OK, 1 row affected (0.00 sec)

查看保存的结果

⑤ 查询结果

mysql> SELECT * FROM my_default;

+------+------+

| name | age |

+------+------+

| NULL | 18 |

| a | 18 |

| b | NULL |

| c | 18 |

+------+------+

4 rows in set (0.00 sec)

为现有的表添加或删除默认约束

① 删除默认约束

mysql> ALTER TABLE my_default MODIFY age INT UNSIGNED;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

② 添加默认约束

mysql> ALTER TABLE my_default MODIFY age INT UNSIGNED DEFAULT 18;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0