· 6 years ago · Mar 06, 2019, 11:52 PM
1column_a column_b column_c column_d
2NULL NULL NULL 1
3NULL 1 NULL 1
4NULL 1 NULL NULL
5NULL NULL NULL NULL
6
7SELECT * FROM (
8 SELECT 'tableA' AS `table`,
9 IF(COUNT(`column_a`), NULL, 'column_a') AS `column`
10 FROM tableA
11UNION ALL
12 SELECT 'tableB' AS `table`,
13 IF(COUNT(`column_b`), NULL, 'column_b') AS `column`
14 FROM tableB
15UNION ALL
16 -- etc.
17) t WHERE `column` IS NOT NULL
18
19SET group_concat_max_len = 4294967295; -- to overcome default 1KB limitation
20
21SELECT CONCAT(
22 'SELECT * FROM ('
23 , GROUP_CONCAT(
24 CONCAT(
25 'SELECT ', QUOTE(TABLE_NAME), ' AS `table`,'
26 , 'IF('
27 , 'COUNT(`', REPLACE(COLUMN_NAME, '`', '``'), '`),'
28 , 'NULL,'
29 , QUOTE(COLUMN_NAME)
30 , ') AS `column` '
31 , 'FROM `', REPLACE(TABLE_NAME, '`', '``'), '`'
32 )
33 SEPARATOR ' UNION ALL '
34 )
35 , ') t WHERE `column` IS NOT NULL'
36 )
37INTO @sql
38FROM INFORMATION_SCHEMA.COLUMNS
39WHERE TABLE_SCHEMA = DATABASE();
40
41PREPARE stmt FROM @sql;
42EXECUTE stmt;
43DEALLOCATE PREPARE stmt;
44
45SELECT Group_concat(Concat( "MAX(", column_name, ")" ))
46 FROM information_schema.columns
47 WHERE table_schema = 'MY_DATABSE'
48 AND table_name = 'MY_TABLE'
49 ORDER BY table_name,ordinal_position
50
51MAX(column_a) MAX(column_b) MAX(column_c) MAX(column_d)
52 NULL 1 NULL 1
53
54CREATE TABLE IF NOT EXISTS `dept` (
55 `did` int(11) NOT NULL,
56 `dname` varchar(50) DEFAULT NULL,
57 PRIMARY KEY (`did`)
58) ENGINE=InnoDB DEFAULT CHARSET=latin1;
59
60
61INSERT INTO `dept` (`did`, `dname`) VALUES
62(1, NULL),
63(2, NULL),
64(3, NULL),
65(4, NULL),
66(5, NULL);
67
68CREATE TABLE IF NOT EXISTS `emp` (
69 `id` int(11) NOT NULL AUTO_INCREMENT,
70 `ename` varchar(50) NOT NULL,
71 `did` int(11) NOT NULL,
72 PRIMARY KEY (`ename`),
73 KEY `deptid` (`did`),
74 KEY `id` (`id`)
75) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
76
77
78INSERT INTO `emp` (`id`, `ename`, `did`) VALUES
79(1, 'e1', 4),
80(2, 'e2', 4),
81(3, 'e3', 2),
82(4, 'e4', 4),
83(5, 'e5', 3);
84
85
86CREATE TABLE IF NOT EXISTS `salary` (
87 `EmpCode` varchar(50) NOT NULL,
88 `Amount` int(11) DEFAULT NULL,
89 `Date` int(11) DEFAULT NULL
90) ENGINE=InnoDB DEFAULT CHARSET=latin1;
91
92INSERT INTO `salary` (`EmpCode`, `Amount`, `Date`) VALUES
93('1', 344, NULL),
94('2', NULL, NULL);
95
96------------------------------------------------------------------------
97------------------------------------------------------------------------
98
99CREATE TABLE IF NOT EXISTS `nullcolumns` (
100 `Table_Name` varchar(100) NOT NULL,
101 `Column_Name` varchar(100) NOT NULL
102) ENGINE=InnoDB DEFAULT CHARSET=latin1;
103
104--Only one procedure Now
105CREATE PROCEDURE get(dn varchar(100))
106BEGIN
107declare c1 int; declare b1 int default 0; declare tn varchar(30);
108declare c2 int; declare b2 int; declare cn varchar(30);
109
110select count(*) into c1 from information_schema.tables where table_schema=dn;
111delete from nullcolumns;
112while b1<c1 do
113select table_name into tn from information_schema.tables where
114table_schema=dn limit b1,1;
115
116select count(*) into c2 from information_schema.columns where
117table_schema=dn and table_name=tn;
118set b2=0;
119while b2<c2 do
120select column_name into cn from information_schema.columns where
121table_schema=dn and table_name=tn limit b2,1;
122
123set @nor := 0;
124set @query := concat("select count(*) into @nor from ", dn,".",tn);
125prepare s1 from @query;
126execute s1;deallocate prepare s1;
127
128if @nor>0 then set @res := 0;
129set @query := concat("select ((select max(",cn,") from ", dn,".",tn,")
130is NULL) into @res");
131prepare s1 from @query;
132execute s1;deallocate prepare s1;
133
134if @res=1 then
135insert into nullcolumns values(tn,cn);
136end if; end if;
137
138set b2=b2+1;
139end while;
140
141set b1=b1+1;
142end while;
143select * from nullcolumns;
144END;
145
146call get('YourDatabaseName');
147
148CREATE TABLE `t1` (
149 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
150 `col_1` int(10) unsigned DEFAULT NULL,
151 `col_2` int(10) unsigned DEFAULT NULL,
152 PRIMARY KEY (`id`)
153) ;
154
155-- let's fill the table with random values
156INSERT INTO t1(col_1,col_2) VALUES(1,2);
157INSERT INTO t1(col_1,col_2)
158SELECT
159IF(RAND() > 0.5, NULL ,FLOOR(RAND()*1000),
160IF(RAND() > 0.5, NULL ,FLOOR(RAND()*1000) FROM t1;
161
162-- run the last INSERT-SELECT statement a few times
163SELECT COUNT(col_1)/COUNT(*) AS col_1_ratio,
164COUNT(col_2)/COUNT(*) AS col_2_ratio FROM t1;
165
166SET @query:=CONCAT("SELECT @column_list:=GROUP_CONCAT(col) FROM (
167SELECT CONCAT('COUNT(',c.COLUMN_NAME,')/COUNT(*)') AS col
168FROM INFORMATION_SCHEMA.COLUMNS c
169WHERE NOT COLUMN_KEY IN('PRI') AND TABLE_SCHEMA=DATABASE()
170AND TABLE_NAME='t1' ORDER BY ORDINAL_POSITION ) q");
171PREPARE COLUMN_SELECT FROM @query;
172EXECUTE COLUMN_SELECT;
173SET @null_counters_sql := CONCAT('SELECT ',@column_list, ' FROM t1');
174PREPARE NULL_COUNTERS FROM @null_counters_sql;
175EXECUTE NULL_COUNTERS;
176
177select length(replace(GROUP_CONCAT(my_col), ',', ''))
178from my_table
179group by my_col
180
181select
182 length(replace(GROUP_CONCAT(col_a), ',', '')) as len_a
183 , length(replace(GROUP_CONCAT(col_b), ',', '')) as len_b
184 , length(replace(GROUP_CONCAT(col_c), ',', '')) as Len_c
185from my_table
186
187select column_name
188from user_tab_columns
189where table_name='Table_name' and num_nulls>=1;