php - 如何通过换行获取字段中的值并更新到php mysql中具有相同id的表中的列？

Question

我想问如何获取该列的值并更新到具有相同 ID 的另一列。该数据来自电子邮件管道，我想将正文的值分开并更新为费用、 Netty 和毛额领域..

我希望你们能帮助我......谢谢

+------+---------+--------------------+---------+----------+-------+----------+
| id   | subject | body               | expense |   net    | gross | email    |
+------+---------+--------------------+---------+----------+-------+----------+
|  1   | Sales   | Expense = 2000     |    0    |    0     |   0   | ex@ex.com|
|      |         | netIncome = 5000   |         |          |       |          | 
|      |         | Gross    = 10000   |         |          |       |          |
+------+---------+--------------------+---------+----------+-------+----------+

Answer 1

假设您有一个 \n 用于换行符，并且 body 具有相同的模式，假设我们有以下内容

mysql> select * from test \G
*************************** 1. row ***************************
  id: 1
body: Expense = 2000
netIncome = 5000
Gross    = 10000

现在要获取每个金额，我们可以使用 substring_index 等内容

mysql> select 
substring_index(substring_index(body,'=',-3),'\n',1) as expense, 
substring_index(substring_index(body,'=',-2),'\n',1) as netincome, 
substring_index(body,'=',-1) as gross from test;
+---------+-----------+--------+
| expense | netincome | gross  |
+---------+-----------+--------+
|  2000   |  5000     |  10000 |
+---------+-----------+--------+

所以对于更新来说可能是这样的

update your_table
set 
expense = substring_index(substring_index(body,'=',-3),'\n',1),
net = substring_index(substring_index(body,'=',-2),'\n',1),
gross = substring_index(body,'=',-1) ;