如何从MySQL中的JSON对象在表中存储键和值 [英] How to store a key and value in a table from a JSON object in MySQL
问题描述
我有一个MySQL数据库表,即ds_message和ds_params,它的表ds_message在每一行中都包含一个JSON对象.我想通过引用ds_message主键id
I'm having a MySQL database tables namely ds_message and ds_params, it table ds_message contains a JSON object in each row. I would like to store the key and value of a JSON object into the table ds_params for all the records by referring the ds_message primary key id
表:ds_message
Table: ds_message
_____________________________________________________________________________________
id key_value
_____________________________________________________________________________________
1 '{"a":"John", "b":"bat", "c":"$10"}'
2 '{"i":"Emma", "j":"Jam"}'
我需要将key_value插入到另一个表中,例如
I'm required to insert the key_value into another table like
_________________________________________________
id message_id json_key json_value
_________________________________________________
1 1 'a' 'John'
2 1 'b' 'bat'
3 1 'c' '$10'
4 2 'i' 'Emma'
5 2 'j' 'Jam'
表结构:
Table Structure:
CREATE TABLE `ds_message` (
`id` int NOT NULL,
`key_value` varchar(500) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='';
ALTER TABLE `ds_message`
ADD PRIMARY KEY (`id`);
INSERT INTO `ds_message` (`id`, `key_value`) VALUES
(1, '{"a":"John", "b":"bat", "c":"$10"}');
INSERT INTO `ds_message` (`id`, `key_value`) VALUES
(2, '{"i":"Emma", "j":"Jam"}');
CREATE TABLE `ds_params` (
`id` int NOT NULL,
`message_id` int NOT NULL,
`json_key` varchar(500) NOT NULL,
`json_value` varchar(500) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='';
ALTER TABLE `ds_params`
ADD PRIMARY KEY (`id`);
请协助我实现此目标,它可以是简单的选择cum插入语句,也可以是通过存储过程以优化方式进行的.
Kindly assist me how to achieve this, it may be a simple select cum insert statement or by stored procedure in an optimized way.
推荐答案
Use MySql JSON functions. The following information will help you writing a stored procedure which fills the table key_value
from ds_message
.
-
要获取对象的键数组,请使用
json_keys
SELECT JSON_keys('{"foo": 1, "bar": 2}')
您可以使用键获取每个属性的值
You can get the value of each property using a key
SELECT JSON_EXTRACT('{"foo": 1, "bar": 2}', '$.foo');
使用嵌套循环对ds_message
记录进行迭代,并使用上述方法对每个json对象进行迭代.为每个对象中的每个键插入一条记录.
Use a nested loop to iterate the ds_message
records and iterate each json object using the above methods. Insert a record for each key in each object.
这篇关于如何从MySQL中的JSON对象在表中存储键和值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!