mysql查询动态更新 [英] mysql query dynamic update

问题描述

<块引用>

已解决

我有一个 mysql 表(table1)，其中有许多列，只有一个 ID.

表名: table1

列:从pt1到pt1000"

id = 1

• 我有一个更新列中数据的查询，一次更新 10 个数据
• 我需要一个独特的动态查询，在每次提交时按顺序插入 10 个值.

示例:

1. 发送查询更新命令(来自php表单)

2. 我在来自pt1"的列中插入 10 个值到pt10"

3. 使用其他数据发送查询更新命令(来自同一个php表单)

4. 我在pt11"的列中插入10个值到pt21"等等

问题:如何设置查询，使其知道将值插入正确的列中，而不总是插入相同的列中?

感谢大家的帮助

------------------- 编辑 -----------------------------

我解决了将手动(输入和选择值)从 php 表单传递到查询的 php 页面的问题，并将此值插入到变量中以构建动态查询.

例如:

PHP 形式:有两个选择

1 - 选择数据库(值 = name_db_i_want_to_update")

2 - 选择列(值 = 1,2,3,4,5,6,7,etc")

在 PHP 查询页面中:

1. 选择 DB 值作为$ db"
2. 选择列为$ set"
3. 带有查询集的 IF 语句.IF 列值 = 1 ELSE IF 等，$ setquery = pt1 = value1"，value2"；等
4. 构造动态查询(UPDATE $db SET $setquery WHERE id=1)

这不是最好的解决方案，作为编程逻辑当然也是错误的，但它确实有效.

感谢大家的帮助

解决方案

您应该将您的设计更改为类似 mytable2 之类的东西，收集数据将需要更多的 sql 编程，但这应该是一次性的以您需要的形式提供您的数据.

如果你坚持保持你的设计，你可以使用像blow这样的存储过程来填充你的表，但你必须知道所属的序列，这也可以自动化，另一个表你已经有了填充的数据位置存储，但是动态sql的基本原理是一样的.

<块引用>

CREATE TABLE mytable2(id int, pos int, value int)

<块引用>

INSERT INTO mytable2 VALUES (1,1,1),(1,2,2),(1,3,3),(1,4,4),(1,5,5),(1,6,6)

<块引用>

CREATE TABLE mytable(id int, pt1 int, pt2 int, pt3 int, pt4 int, pt5 int, pt6 int)

<块引用>

CREATE PROCEDURE `new_routine` (_id int, _sequence int, _pt1 int, _pt2 int,_pt3 int)开始IF _sequence = 0 那么SET @sql := CONCAT('INSERT INTO mytable (id,pt1,pt2,pt3) VALUES (',_id,',',_pt1,',',_pt2,',',_pt3,')');别的SET @sql := CONCAT('UPDATE mytable SET pt',1 +((_sequence -1) * 3) ,'=',_pt1,', pt',2 +((_sequence -1) * 3) ,'=',_pt2,', pt',3 +((_sequence -1) * 3) ,'=',_pt3,' WHERE id = ',_id);万一;从@sql 准备 stmt;执行 stmt;解除分配准备 stmt;结尾

<块引用>

CALL new_routine(1,0,1,1,1);

<块引用>

CALL new_routine(1,1,2,2,2)

<块引用>

CALL new_routine(1,2,3,3,3)

<块引用>

SELECT * FROM mytable

<前>身份证 |pt1 |pt2 |pt3 |pt4 |pt5 |pt6-: |--: |--: |--: |--: |--: |——:1 |2 |2 |2 |3 |3 |3

<块引用>

SELECT * FROM mytable2

<前>身份证 |位置 |价值-: |--: |----:1 |1 |11 |2 |21 |3 |31 |4 |41 |5 |51 |6 |6

db<>fiddle 这里

RESOLVED

I have a mysql table (table1) with many columns with a single ID.

table name: table1

columns: from "pt1 to pt1000"

id = 1

• I have a query that updates the data in the columns, 10 data at a time
• I would need a unique and dynamic query that inserts the 10 values ​​in sequence at each submission.

example:

1. send query update command (from php form)

2. I insert the 10 values ​​in the columns from "pt1" to "pt10"

3. send query update command with other data (from the same php form)

4. I insert the 10 values ​​in the columns from "pt11" to "pt21" and so on

Question: How can I set the query so that it knows to insert the values ​​in the right columns and not always in the same ones?

Thanks to all help me

------------------ EDIT -----------------------------

I resolve passing manual (input and select value) from php form to php page of query and insert this value in variable to construct dynamical query.

For example:

IN PHP FORM: have two select

1 - Select DB (value = "name_db_i_want_to_update")

2 - Select Column ( value = "1,2,3,4,5,6,7,etc")

IN PHP QUERY PAGE:

1. Select DB Value as "$ db"
2. Select Column as "$ set"
3. IF statement with piece of query set. IF column value = 1 ELSE IF etc, $ setquery = pt1 = "value1","value2" etc.
4. Construct dynamical query ( UPDATE $db SET $setquery WHERE id=1 )

it is not the best solution, certainly also wrong as a programming logic, but it works.

Thanks to all for help

解决方案

You shouild change your design to somethingk like mytable2, to gather the data will be a bit more sql programming needed, but it should be a one time thing to get your data in the form you need.

If you insist on keeping your design, you can use a stored procedure like blow to fill your table, but you have to know the sequence where the belong, this could be automated as well, with another table wherre you have the already filled data position stored , but the basic principle of dynamic sql is the same.

CREATE TABLE mytable2(id int, pos int, value int)

INSERT INTO mytable2 VALUES (1,1,1),(1,2,2),(1,3,3),(1,4,4),(1,5,5),(1,6,6)

CREATE TABLE mytable(id int, pt1 int, pt2 int, pt3 int, pt4 int, pt5 int, pt6 int)

CREATE PROCEDURE `new_routine` (_id int, _sequence int, _pt1 int, _pt2 int,_pt3 int)
BEGIN

    IF _sequence = 0 then
       SET @sql := CONCAT('INSERT INTO mytable (id,pt1,pt2,pt3) VALUES (',_id,',',_pt1,',',_pt2,',',_pt3,')');
    ELSE
       SET @sql := CONCAT('UPDATE mytable SET pt',1 +((_sequence -1) * 3) ,'= ',_pt1
                          ,', pt',2 +((_sequence -1) * 3) ,'= ',_pt2
                          ,', pt',3 +((_sequence -1) * 3) ,'= ',_pt3,' WHERE id = ',_id);
    END IF;

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END

CALL new_routine(1,0,1,1,1);

CALL new_routine(1,1,2,2,2)

CALL new_routine(1,2,3,3,3)

SELECT * FROM mytable

id | pt1 | pt2 | pt3 | pt4 | pt5 | pt6
-: | --: | --: | --: | --: | --: | --:
 1 |   2 |   2 |   2 |   3 |   3 |   3

SELECT * FROM mytable2

id | pos | value
-: | --: | ----:
 1 |   1 |     1
 1 |   2 |     2
 1 |   3 |     3
 1 |   4 |     4
 1 |   5 |     5
 1 |   6 |     6

db<>fiddle here

这篇关于mysql查询动态更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！