-- 建表,设置校验
CREATE TABLE geo (
coordinate JSON,
CHECK(
JSON_SCHEMA_VALID(
'{
"type":"object",
"properties":{
"latitude":{"type":"number", "minimum":-90, "maximum":90},
"longitude":{"type":"number", "minimum":-180, "maximum":180}
},
"required": ["latitude", "longitude"]
}',
coordinate
)
)
)ENGINE = InnoDB AUTO_INCREMENT = 10000 DEFAULT CHARSET = utf8mb4 COMMENT = '地理信息';
INSERT INTO `geo` (`coordinate`) VALUES ('{"latitude":80,"longitude":90}');
修改已有表的校验
-- car_info表的car_detail字段增加校验
ALTER TABLE car_info ADD CONSTRAINT CHECK(
JSON_SCHEMA_VALID(
'{
"$schema": "http://json-schema.org/draft-04/schema#",
"type": "object",
"properties": {
"tags": {
"type": "array",
"items": { "type": "string" }
}
},
"additionalProperties": false
}',
car_detail
)
);