在数据库mysql中更新和删除数据 [英] Update and delete data in database mysql

问题描述

我使用 codeigniter 。我有以下值作为数据库行中的json_encode，我想删除值 3 在行1和值 14 5 。

DELETE - > value 3 在行2上的值 14 ， 5

UPDATE - > value 2 到 8 值 3 至 17 。

是查询值json吗？

第1行：

  id = 1 
 
 [{
hotel_id：[3] 
}，{
hotel_id：[2] 
} ，{
hotel_id：[1] 
}] 
  

第2行：

  id = 2 
 
 [{
hotel_id：[ 14] 
}，{
hotel_id：[5] 
}，{
hotel_id：[4] 
} 
hotel_id：[3] 
}，{
hotel_id：[2] 
}，{
hotel_id 1] 
}] 
  

解决方案的评论者试图说，你的问题是一个非常常见的存储序列化数据在DB（如JSON字符串） - 这就是为什么这被认为是坏习惯。
关系数据库的所有精彩工具，如索引，排序，分组和根据任何列查询，一旦数据存储为一大堆字符数据库无法解析，就会消失。

解决方案是将实际的DATA保存在DB中，并在应用程序端将其格式化为JSON，以便在需要时使用。
在你的情况下，你可以创建一个表hotels_to_users（我在这里做一些猜测你的数据的意义，希望你得到它，即使它不是你所需要的）。此表格将如下所示：

  user_id hotel_id 
 1 | 1 
 1 | 2 
 2 | 14 
  

这将使得查询，更新，插入和删除任何特定酒店。

希望这是有意义的，你可以做出这样的改变，否则你的问题可以通过一些MySQL字符串函数，如REPLACE和SUBSTR，或者做所有在应用程序代码中的工作，并在完成后保存新的JSON。无论如何，维持它将是困难的，需要更多的努力在长期。

I use codeigniter. i have following values as json_encode in database row, i want delete value 3 in row 1 and values 14 & 5 in database table.

DELETE -> value 3 on row 1 & values 14, 5 on rows 2
UPDATE -> value 2 to 8 on row 1 & value 3 to 17 on rows 2.

How is query it for values json?

Row 1:

id=1

[{
    "hotel_id": ["3"]
}, {
    "hotel_id": ["2"]
}, {
    "hotel_id": ["1"]
}]

Row 2:

id=2

[{
    "hotel_id": ["14"]
}, {
    "hotel_id": ["5"]
}, {
    "hotel_id": ["4"]
}, {
    "hotel_id": ["3"]
}, {
    "hotel_id": ["2"]
}, {
    "hotel_id": ["1"]
}]

解决方案

What most of the commenters are trying to say is that your problem is a very common one when storing serialized data in the DB (like JSON strings) - That is exactly why this is considered bad practice. All the wonderful tools of relational databases, like indexing, sorting, grouping and querying according to any column are gone once the data is stored as a big pile of characters the DB can't parse.

The solution is to save the actual DATA in the DB, and format it as JSON only on the application side, for use when needed. In your case, you can create a table like hotels_to_users (I am doing some guesswork here about the meaning of your data, hoping you get it even if it's not exactly what you need). This table will look like:

user_id (?) |  hotel_id
1           |  1
1           |  2
2           |  14

This will make it very easy to query, update, insert and delete any specific hotel to any user.

Hope this makes sense and that you are able to make that change, otherwise your problem can be solved with either some MySQL string functions like REPLACE and SUBSTR, or by doing all the work in the application code and saving a new JSON when done. Either way, maintaining it will be hard and require more effort in the long term.

这篇关于在数据库mysql中更新和删除数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！