之前第一次做国际化项目,前期数据库是同事设计的,他的想法是,需要多语言的字段,全部用多个字段,如 name 存储中文姓名,english_name存储英文姓名,如果项目始终固定两种语言,这种设计完全没问题,但一旦需要新增语言,所有涉及到国际化的字段,全部要新增一个字段!
后来重新想了个方案,mysql5.7支持json数据类型,需要国际化的字段,全部使用json类型,存储的数据示例:
name字段需要国际化,使用json类型:
{
"cn":"疼样",
"en":"Teng yang",
"other":"*……&¥#87"
}
//or
{
"name_cn":"疼样",
"name_en":"Teng yang",
"name_other":"*……&¥#87"
}
后面公司新项目,涉及国际化的,会尝试使用这种方案,不确定会不会遇到什么坑,走着瞧,O(∩_∩)O哈哈~
最后来几条json类型sql操作:
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` json DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO `test` (`name`) VALUES ('{"cn":"疼样","en":"Teng yang","other":"*……&¥#87"}');
SELECT json_extract(`name`,'$.en') AS `name` FROM `test`;
SELECT `name` -> '$.zh' AS `name` FROM `test`;
SELECT * FROM test WHERE `name` -> '$.en' LIKE '%key%';
UPDATE `test` SET `name`=json_set(`name`,'$.en','Ty');
UPDATE `test` SET `name`='{"cn":"疼样","en":"TY","other":"no"}';
JSON数组查询:
SELECT JSON_EXTRACT(order_details ,'$[0]') AS roomName FROM test;
SELECT JSON_EXTRACT(order_details ,'$[0].roomName') AS roomName FROM test;
SELECT order_details -> '$[0].roomName' AS roomName FROM test;
-- 去 引号
SELECT order_details ->> '$[0].roomName' AS roomName FROM test;
SELECT JSON_UNQUOTE(order_details -> '$[0].roomName') FROM b_room_order
SELECT JSON_SEARCH('[1,2,3]','one', '1');
SELECT JSON_CONTAINS('[1,2,3]', '[1, 2]');