删除测试用的手机号/客户/店铺/订单/询价/报价
SELECT table_name name,TABLE_COMMENT value FROM INFORMATION_SCHEMA.TABLES WHERE
table_schema = ‘hpqsharecloud’ order by table_name asc
select ORDINAL_POSITION as Colorder,
Column_Name as ColumnName,
data_type as TypeName,
COLUMN_COMMENT as DeText,
(case when data_type = ‘float’ or data_type = ‘double’ or data_type = ‘decimal’ then NUMERIC_PRECISION else CHARACTER_MAXIMUM_LENGTH end ) as length,
NUMERIC_SCALE as Scale,( case when EXTRA=’auto_increment’ then 1 else 0 end) as IsIdentity,(case when COLUMN_KEY=’PRI’ then 1 else 0 end) as IsPK,
(case when IS_NULLABLE = ‘NO’ then 0 else 1 end)as CanNull,COLUMN_DEFAULT as DefaultVal
from information_schema.columns where table_schema = ‘hpqsharecloud’ and table_name = ‘users’ order by ORDINAL_POSITION asc
set @custtel = ‘18832116501,18631139906’;
select concat(‘delete from ‘,table_name,’ where ‘,column_name,’ = ‘’’,cust.custno,’’’;’) as sqltext
from information_schema.columns,
(select custno from customer where FIND_IN_SET(custtel ,@custtel) ) cust
where table_schema = ‘hpqsharecloud’ and column_name = ‘custno’;
select concat(‘delete from ‘,table_name,’ where ‘,column_name,’ = ‘’’,cust.shopno,’’’;’) as sqltext
from information_schema.columns,
(select shopno from customer where FIND_IN_SET(custtel ,@custtel ) and ifnull(shopno,’’)!=’’ ) cust
where table_schema = ‘hpqsharecloud’ and column_name = ‘shopno’;
select concat(‘delete from ‘,table_name,’ where FIND_IN_SET( ‘,column_name,’ ,in_custno); ‘) as sqltext
from information_schema.columns
where table_schema = ‘hpqsharecloud’ and column_name = ‘custno’;
select concat(‘delete from ‘,table_name,’ where ‘,column_name,’ in (select shopno from _tmpTableShop ); ‘) as sqltext
from information_schema.columns
where table_schema = ‘hpqsharecloud’ and column_name = ‘shopno’;
select concat(‘delete from ‘,table_name,’ where ‘,column_name,’ = ‘’’,cust.custno,’’’;’) as sqltext
from information_schema.columns,
(select custno from customer where FIND_IN_SET(custtel ,@custtel) ) cust
where table_schema = ‘hpqsharecloud’ and FIND_IN_SET(column_name,’custno,inquirycustno’);
select concat(‘delete from ‘,table_name,’ where ‘,column_name,’ = ‘’’,cust.shopno,’’’;’) as sqltext
from information_schema.columns,
(select shopno from customer where FIND_IN_SET(custtel ,@custtel ) and ifnull(shopno,’’)!=’’ ) cust
where table_schema = ‘hpqsharecloud’ and FIND_IN_SET(column_name,’shopno,offershopno’);
select concat(‘delete from ‘,table_name,’ where FIND_IN_SET( ‘,column_name,’ ,in_custno); ‘) as sqltext
from information_schema.columns
where table_schema = ‘hpqsharecloud’ and FIND_IN_SET(column_name,’custno,inquirycustno’);
select concat(‘delete from ‘,table_name,’ where ‘,column_name,’ in (select shopno from _tmpTableShop ); ‘) as sqltext
from information_schema.columns
where table_schema = ‘hpqsharecloud’ and FIND_IN_SET(column_name,’shopno,offershopno’);