php - UPDATE 查询中的通配符列名

Question

假设我有一个名为 techsched 的 SQL 表设置如下，但 800,900 等一直持续到 2300，每个代表一个小时的时间 block 。六个字符的字母数字字符 (TPI232) 代表分配给技术的工单号，并且工单号在技术安排的每个时间 block 中重复。我需要运行一个 SQL 查询，无论票号位于 TABLE 中的哪个位置，它都可以找到票号，并将找到票号的行/列的值设置为 NULL 或 BLANK

Tech         date          800      900      1000      1100      1200John Doe     05-01-15                       DSA123    DSA123    DSA123Mike Doe     05-01-15     FGG342   FGG342  Bill Doe     05-01-15                      Steve Doe    05-01-15              TPI232   TPI232    TPI232  

我知道下面这个不会也不会起作用，但它是展示我正在尝试做的事情的最佳方式:

UPDATE techsched SET wildcard_column_name='' WHERE wildcard_column_name='FGG342'

我不知道记录号可能出现在表中的什么位置，那么如何实现呢？

Answer 1

像这样创建一个存储过程并调整它以适合您的情况:

delimiter $$

drop procedure if exists clear_values$$

create procedure clear_values(subject char(10))
begin

    declare finished int default 0;
    declare colname varchar(100);

    -- cursor
    declare cur_columns cursor for
        select column_name
        from information_schema.columns
        where table_name = 'test'
        and data_type = 'char'
        and character_maximum_length = 10;
    -- data type and length matches the field info
    -- in my table

    -- handler for when we run out of records to read
    declare continue handler for not found
        set finished = 1;

    open cur_columns;
    reading: loop

        -- retrieve data until end of records
        fetch cur_columns into colname;
        if finished = 1 then
            leave reading;
        end if;

        -- create text that will update column's value
        set @statement = concat(
            'update test ',
            'set `', colname, '` = \'\' ',
            'where `', colname, '` = \'', subject, '\''
            );

        -- create a prepared statement from the text 
        -- and execute it
        prepare stmt from @statement;
        execute stmt;
        deallocate prepare stmt;

    end loop reading;
    close cur_columns;

end$$

delimiter ;

如果有机会，假设这是一个小项目，请考虑对表进行某种程度的规范化:

create table techs (
  id int auto_increment primary key,
  tech varchar(50)
);

create table schedules (
  id int auto_increment primary key,
  tech_id int not null,
  sched datetime not null,
  ticket char(6),
  constraint fk_schedules_techs_tech_id
    foreign key (tech_id)
    references techs (id),
  constraint uk_schedules_tech_id_sched 
    unique (tech_id, sched)
);

insert into techs (tech) values 
('Joe'), 
('Matt');

insert into schedules (tech_id, sched, ticket) values 
(1, '2015-05-01 08:00:00', ''), 
(1, '2015-05-01 09:00:00', ''),
(1, '2015-05-01 10:00:00', 'DSA123'),
(2, '2015-05-01 08:00:00', 'FGG324'), 
(2, '2015-05-01 09:00:00', 'FGG324'),
(2, '2015-05-01 10:00:00', '');

http://sqlfiddle.com/#!9/19bc3/1

现在，当您必须清除票据为 FGG324 的票据时，您可以键入:

update schedules set ticket = '' where ticket = 'FGG324';