Найти все те столбцы, которые имеют только нулевые значения, в таблице MySQL

Ответ 1

Вы можете избежать использования процедуры путем динамического создания (из таблицы INFORMATION_SCHEMA.COLUMNS) строки, содержащей SQL, которую вы хотите выполнить, а затем подготовка инструкции из этой строки и ее выполнение.

SQL, который мы хотим построить, будет выглядеть так:

SELECT * FROM (
  SELECT 'tableA' AS `table`,
         IF(COUNT(`column_a`), NULL, 'column_a') AS `column`
  FROM   tableA
UNION ALL
  SELECT 'tableB' AS `table`,
         IF(COUNT(`column_b`), NULL, 'column_b') AS `column`
  FROM   tableB
UNION ALL
  -- etc.
) t WHERE `column` IS NOT NULL

Это можно сделать, используя следующее:

SET group_concat_max_len = 4294967295; -- to overcome default 1KB limitation

SELECT CONCAT(
         'SELECT * FROM ('
       ,  GROUP_CONCAT(
            'SELECT ', QUOTE(TABLE_NAME), ' AS `table`,'
          , 'IF('
          ,   'COUNT(`', REPLACE(COLUMN_NAME, '`', '``'), '`),'
          ,   'NULL,'
          ,    QUOTE(COLUMN_NAME)
          , ') AS `column` '
          , 'FROM `', REPLACE(TABLE_NAME, '`', '``'), '`'
          SEPARATOR ' UNION ALL '
         )
       , ') t WHERE `column` IS NOT NULL'
       )
INTO   @sql
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_SCHEMA = DATABASE();

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

Посмотрите на sqlfiddle.

Ответ 2

Я не являюсь экспертом в процедурах SQL, поэтому даю общую идею, используя SQL-запросы и PHP/python script.

• используйте SHOW TABLES или какой-либо другой запрос в базе данных INFORMATION_SCHEMA, чтобы получить все таблицы в вашей базе данных MY_DATABASE

• выполнить запрос для создания оператора для получения всех имен столбцов в определенной таблице, это будет использоваться в следующем запросе.

 SELECT Group_concat(Concat( "MAX(", column_name, ")" ))
         FROM   information_schema.columns
         WHERE  table_schema = 'MY_DATABSE'
                AND table_name = 'MY_TABLE'
         ORDER  BY table_name,ordinal_position

• Вы получите результат, например MAX(column_a),MAX(column_b),MAX(column_c),MAX(column_d)

• Используйте этот вывод для генерации окончательного запроса:

SELECT Max (column_a), Макс (столбец_b), Макс (столбец_c), Макс (столбец_d) FROM MY_DATABASE.MY_TABLE

Вывод будет:

   MAX(column_a)    MAX(column_b)   MAX(column_c)   MAX(column_d)
     NULL            1           NULL                1

• Все столбцы с максимальным значением NULL - это те, которые имеют все значения NULL

Ответ 3

Демо-ссылка SQL Fiddle

Я создал 4 таблицы. Три для демонстрации и одна nullcolumns являются обязательной частью решения. Среди трех таблиц только salary и dept имеют столбцы со всеми значениями null (вы можете посмотреть их script).

Обязательная таблица и процедура даны в конце

Вы можете скопировать вставку и запустить (обязательную часть или все) как sql (просто вам нужно изменить разделитель на // ) в вашей нужной базе данных на вашем локальном хосте, а затем --- call get(); и посмотреть результаты

CREATE TABLE IF NOT EXISTS `dept` (
  `did` int(11) NOT NULL,
  `dname` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`did`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `dept` (`did`, `dname`) VALUES
(1, NULL),
(2, NULL),
(3, NULL),
(4, NULL),
(5, NULL);

CREATE TABLE IF NOT EXISTS `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ename` varchar(50) NOT NULL,
  `did` int(11) NOT NULL,
  PRIMARY KEY (`ename`),
  KEY `deptid` (`did`),
  KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;


INSERT INTO `emp` (`id`, `ename`, `did`) VALUES
(1, 'e1', 4),
(2, 'e2', 4),
(3, 'e3', 2),
(4, 'e4', 4),
(5, 'e5', 3);


CREATE TABLE IF NOT EXISTS `salary` (
  `EmpCode` varchar(50) NOT NULL,
  `Amount` int(11) DEFAULT NULL,
  `Date` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `salary` (`EmpCode`, `Amount`, `Date`) VALUES
('1', 344, NULL),
('2', NULL, NULL);

------------------------------------------------------------------------
------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `nullcolumns` (
  `Table_Name` varchar(100) NOT NULL,
  `Column_Name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--Only one procedure Now
CREATE PROCEDURE get(dn varchar(100))
BEGIN
declare c1 int; declare b1 int default 0; declare tn varchar(30);
declare c2 int; declare b2 int; declare cn varchar(30);

select count(*) into c1 from information_schema.tables where table_schema=dn;
delete from nullcolumns;
while b1<c1 do
select table_name into tn from information_schema.tables where
table_schema=dn limit b1,1;        

select count(*) into c2 from information_schema.columns where
table_schema=dn and table_name=tn;
set b2=0;
while b2<c2 do
select column_name into cn from information_schema.columns where
table_schema=dn and table_name=tn limit b2,1;

set @nor := 0;
set @query := concat("select count(*) into @nor from ", dn,".",tn);
prepare s1 from @query;
execute s1;deallocate prepare s1;

if @nor>0 then set @res := 0;
set @query := concat("select ((select max(",cn,") from ", dn,".",tn,")
is NULL) into @res");
prepare s1 from @query;
execute s1;deallocate prepare s1;

if @res=1 then
insert into nullcolumns values(tn,cn);
end if; end if;

set b2=b2+1;
end while;

set b1=b1+1;
end while;
select * from nullcolumns;
END;

Вы легко можете легко выполнить хранимую процедуру как sql в своем phpmyadin ', так как это просто измените разделители (внизу блока SQL quesry) на //Then

call get();

И Наслаждайтесь:)

Теперь вы можете увидеть таблицу nullcolumns, отображающую все столбцы со 100/100 нулевыми значениями вместе с таблицей Имена

В коде процедуры if @nor>0 ограничивается, что пустая таблица не должна включаться в результаты, вы можете удалить это ограничение.

Ответ 4

Вы можете использовать поведение функции агрегации COUNT в отношении NULL. Передавая поле в качестве аргумента, функция COUNT возвращает количество значений, отличных от NULL, в то время как COUNT (*) возвращает общее количество строк. Таким образом, вы можете рассчитать отношение NULL к "приемлемым" значениям.

Я приведу пример со следующей структурой таблицы:

CREATE TABLE `t1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `col_1` int(10) unsigned DEFAULT NULL,
   `col_2` int(10) unsigned DEFAULT NULL,
   PRIMARY KEY (`id`)
) ;

-- let fill the table with random values
INSERT INTO t1(col_1,col_2) VALUES(1,2);
INSERT INTO t1(col_1,col_2) 
SELECT 
IF(RAND() > 0.5, NULL ,FLOOR(RAND()*1000), 
IF(RAND() > 0.5, NULL ,FLOOR(RAND()*1000) FROM t1;

-- run the last INSERT-SELECT statement a few times
SELECT COUNT(col_1)/COUNT(*) AS col_1_ratio, 
COUNT(col_2)/COUNT(*) AS col_2_ratio FROM t1;

Вы можете написать функцию, которая автоматически создает запрос из INFORMATION_SCHEMA, передав имя таблицы в качестве входной переменной. Здесь, как получить данные структуры непосредственно из таблиц INFORMATION_SCHEMA:

SET @query:=CONCAT("SELECT @column_list:=GROUP_CONCAT(col) FROM (
SELECT CONCAT('COUNT(',c.COLUMN_NAME,')/COUNT(*)') AS col
FROM INFORMATION_SCHEMA.COLUMNS c 
WHERE NOT COLUMN_KEY IN('PRI') AND TABLE_SCHEMA=DATABASE() 
AND TABLE_NAME='t1' ORDER BY ORDINAL_POSITION ) q");
PREPARE COLUMN_SELECT FROM @query;
EXECUTE COLUMN_SELECT;
SET @null_counters_sql := CONCAT('SELECT ',@column_list, ' FROM t1');
PREPARE NULL_COUNTERS FROM @null_counters_sql;
EXECUTE NULL_COUNTERS;

Ответ 5

Я думаю, вы можете сделать это с помощью GROUP_CONCAT и GROUP BY:

select length(replace(GROUP_CONCAT(my_col), ',', ''))
from my_table
group by my_col

(непроверенные)

EDIT: документы, похоже, не указывают, что GROUP_CONCAT нуждается в соответствующей GROUP BY, поэтому попробуйте следующее:

select 
    length(replace(GROUP_CONCAT(col_a), ',', '')) as len_a
    , length(replace(GROUP_CONCAT(col_b), ',', '')) as len_b
    , length(replace(GROUP_CONCAT(col_c), ',', '')) as Len_c
from my_table

Ответ 6

select column_name
from user_tab_columns
where table_name='Table_name' and num_nulls>=1;

Просто с помощью простого запроса вы получите эти два столбца.