如何通过换行获取字段中的值，并在 php mysql 中更新到具有相同 id 的表中的列中 - How to get the value in field by new line and update into the column in table with same id in php mysql?

How to get the value in field by new line and update into the column in table with same id in php mysql?

我想问如何获取列的值并更新到具有相同ID的另一列中。这些数据来自电子邮件管道，我想分离正文的值并更新到费用，净值和总值字段中。

我希望你能帮助我，谢谢

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

假设您有一个换行符'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) ;