Soru MySQL tablosunda yalnızca boş değerlere sahip olan tüm sütunları bulun


Durum aşağıdaki gibidir:

Her birinde önemli sayıda sütun bulunan önemli sayıda tablo var. Bu eski ve kabul edilmeyen veritabanını yeni bir sistemle ele almam gerekiyor ve görünüşe göre hiç kullanılmamış olan tüm sütunları ortadan kaldırmanın bir yolunu arıyorum.

Herhangi bir satırdaki bir değere sahip tüm sütunları filtreleyerek, tüm satırlarda değerin NULL olduğu bir sütun kümesiyle birlikte bırakarak bunu yapmak istiyorum. Tabii ki, her bir sütunu azalan sıraya göre sıralayabilirim, ama bu çok fazla zaman alıyor çünkü bir sürü masa ve sütunla uğraşıyorum. Tablo başına 50 (!) Sütun ile 400 tablo olduğunu tahmin ediyorum.

Bu bilgiyi information_schema'dan alabilmem mümkün mü?

DÜZENLE:

İşte bir örnek:

column_a    column_b    column_c    column_d
NULL        NULL        NULL        1
NULL        1           NULL        1
NULL        1           NULL        NULL
NULL        NULL        NULL        NULL

Çıktı, herhangi bir değer içermeyen tek sütun olduğu için 'column_a' ve 'column_c' olmalıdır.


19
2017-08-23 12:10


Menşei


Bence herhangi bir tek sorgu ile çözülmek için çok zor. Bir prosedüre ihtiyacın var. Bir prosedür veya sadece sorgu ile bunu yapmak kabul edilebilir mi? - Sami
Hiç sorun değil, daha iyi olabilir çünkü daha sonra kolayca başka bir tablo adı geçebilir. - Sherlock


Cevaplar:


Dinamik olarak oluşturarak bir yordam kullanmaktan kaçabilirsiniz ( INFORMATION_SCHEMA.COLUMNS Tablo) yürütmek istediğiniz SQL'i içeren bir dize bir bildiri hazırlamak Bu dizeden ve onu yürütme.

Oluşturmak istediğimiz SQL şöyle görünecektir:

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

Bu aşağıdaki kullanılarak yapılabilir:

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;

Gör sqlfiddle.


17
2017-08-30 10:03



Örneğiniz çalışmasına rağmen (SQLFiddle üzerinde), veritabanım için çok ağır. Şöyle diyor: bellek hemen tükendi ... Önerilen yöntemle bunun etrafında bir yol olduğunu düşünmüyorum. - Sherlock
@ Robinv.G .: Belki de MySQL'in konfigürasyon parametrelerinin değiştirilmesi ve / veya sunucuya daha fazla bellek eklenmesi dışında ... ama evet, çok büyük bir sorgu oluşturacak ~ 400 * 50 = 20k sütunlarını araştırmak istiyorsunuz. Tek bir sorgu çok büyükse, bir kerede tablonuzun bir alt kümesine odaklanabilirsiniz. değiştirerek WHERE yan tümce TABLE_SCHEMA = DATABASE() AND TABLE_NAME BETWEEN 'A' AND 'C'. Veya başka bir döngü yapısı kullanmanız gerekecek. saklı bir prosedürde - başka yolu yoktur. - eggyal
Efsane için teşekkür ederim, onunla biraz oynayacağım! - Sherlock
Bunu masam için masaya koyuyorum, ki bu da ihtiyaçlar için mükemmel bir sonuç. Tablo için tablo yapmak sorun değil. Tekrar teşekkürler! - Sherlock
@eggyal. Lütfen bu bağlantıdaki sorumu ve çözümümü kontrol edin stackoverflow.com/questions/12556713/... . Bu yazıya verdiğim cevaplara benziyor. Bu yazıya benzemen için bu cevaba ihtiyacım var :) - Sami


SQL prosedürlerinde uzman değilim, bu yüzden SQL sorguları ve bir PHP / python betiği kullanarak genel bir fikir verin.

  • kullanım SHOW TABLES veya başka bir sorguda INFORMATION_SCHEMA veritabanındaki tüm tabloları almak için veritabanı MY_DATABASE

  • Belirli bir tabloda tüm sütun adlarını almak için bir deyim oluşturmak için bir sorgu yapın, bu sonraki sorguda kullanılacaktır.

 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
  • Gibi bir çıktı alacak MAX(column_a),MAX(column_b),MAX(column_c),MAX(column_d)

  • Son sorguyu oluşturmak için bu çıkışı kullanın:

Maks (sütun_a), Maks (sütun_b), Maks (sütun_c), Maks (sütun_d) MY_DATABASE.MY_TABLE FROM

Çıkış şöyle olurdu:

   MAX(column_a)    MAX(column_b)   MAX(column_c)   MAX(column_d)
     NULL            1           NULL                1
  • Max değerine sahip tüm sütunlar NULL tüm değerlere sahip olanlar NULL

10
2017-08-27 07:10



Bu ilginç bir yaklaşımdır (bunun için: +1), ama biraz tatmin edici değil. Gerçekten hepsini bir arada yapıştırmak için bir dil gerektirir. Saf SQL'de (bu yöntemi bir SP'de kapsüllemek) mümkün olabilir, ancak muhtemelen uzun ve çirkin olmaktan çıkar. Bunu kabul etmeden önce diğer cevapları bekleyeceğim. Bu yaklaşım için teşekkürler tho. :) - Sherlock
@ Robinv.G. Bir Depolanmış Prosedürde kesinlikle mümkündür, ancak bir betik dili daha fazla kontrol ve daha temiz akış sağlar. - DhruvPathak


SQL Fiddle Demo Bağlantısı

4 tablo oluşturdum. Demo ve üç için nullcolumns Çözümün zorunlu kısmıdır. Sadece üç tablo arasında salary ve dept null değerleri olan sütunlara sahip olmalısınız (betiklerine bakabilirsiniz).

Zorunlu tablo ve prosedür sonunda verilir.

Yapıştır ve kopyala (zorunlu bölüm veya tümünü) sql olarak (sadece sınırlayıcıyı // 'ye değiştirmek zorundasın) yerel veritabanında istediğin veritabanına yazıp --- call get(); ve sonuçları gör

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;

Saklı yordamı kolayca phpmyadin 'inizde sql olarak kolayca yürütebilirsiniz,' sadece '' ayırıcıları '' (SQL sorgu kutusunun en altında) olarak değiştirin.

call get();

Ve Keyfini çıkarın :)

Şimdi masayı görebilirsin nullcolumns tablo adları ile birlikte 100/100 boş değerlere sahip tüm sütunları gösterme

Prosedür kodunda if @nor>0 Boş bir tablonun sonuçlara dahil edilmemesi gerektiğini kısıtlar, bu kısıtlamayı kaldırabilirsiniz.


5
2017-08-28 21:06



Eğer saklı prosedürlerle veya başka bir şeyle karşılaşırsanız, daha fazla yol göstermekten memnuniyet duyarız. - Sami
Vay, bu gerçekten düzgün bir SQL parçasıdır! Şimdi MySQL üzerinde çalışmak için mücadele ediyorum (iç ...) (LIMIT B, 1 çalışmıyor), ama bu iyi bir iş! - Sherlock
Kodu üç yerine bir işlem yapmak için düzenledim. Bir prosedür ayrıca sqlfiddle @ Robinv.G'de demo yüklememe izin verdi. - Sami
Bunu MySQL'de test etme şansın var mı? Bunu işe alamıyorum. :( - Sherlock
@ Robinv.G. Eggyal'ın çözümü daha iyi görünüyor. Benim kodum ayrıca sqlfiddle üzerinde çalışıyor olmasına rağmen bakmış olabilirsiniz. Burada değildim. Aksi halde, şemanını yükleyebilirsen, senin için çalışabilirdim. Ancak, üzücü olan şey, ilk olarak benim için iyi bir şans olduğunu ve bunun için çok çaba sarf ettim ve bunun için çaba harcadım. Ama iyi, bu çaba sırasında birçok şey öğrendim. Çok hoş tepki için teşekkürler. Öğrenim avantajı ve güzel cevabınız ile harcanan saatlerimi ödüllendirdim :) - Sami


Davranışından yararlanabilirsiniz. COUNT NULL'lerle ilgili toplama işlevi. Alanı argüman olarak geçirerek COUNT işlevi, NULL olmayan değerlerin sayısını döndürür (*) COUNT toplam satır sayısını döndürür. Böylece NULL oranını "kabul edilebilir" değerlere hesaplayabilirsiniz.

Aşağıdaki tablo yapısına bir örnek vereceğim:

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's 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;

Bir sorguyu otomatik olarak oluşturan bir işlev yazabilirsiniz. Tablo adını giriş değişkeni olarak geçirerek INFORMATION_SCHEMA veritabanı. Yapı verilerini doğrudan BİLGİDEME tablolarından nasıl elde edeceğiniz aşağıda açıklanmıştır:

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;

4
2017-08-27 14:41



Bu gayet iyi görünüyor, ancak bunu MySQL üzerinde çalışamıyorum. Buna yarın da kalacağım. Son satırın ikincisi bir sözdizimi hatası verir. - Sherlock
Cevabımı inceledim, lütfen tekrar kontrol edin. - wisefish
Onunla oynuyordum ve tüm COUNT'ları birleştiriyor, ancak bir sorgu olarak çalışmıyor. Aldığım çıktı şu: COUNT(column)/COUNT(*) her sütun için. Aslında yürütmez. Nereye bakacağını biliyor musun? - Sherlock
Ayrıca, bana "COUN" un bilinmeyen bir sütun olduğunu bildiren bir uyarı (1, 2, 4 ve 5 sorguları için) veriyor. "COUNT", "COUNT" değil. Garip bir şey: Ben hiçbir yerde eksik T ile "COUN" bulamıyorum .. - Sherlock
Sorguyu kendisi tarafından INFORMATION_SCHEMA üzerinde çalıştırmayı deneyin. SELECT CONCAT ('COUNT (', c.COLUMN_NAME, ') / COUNT (*)') (Bu bağlantı, bir kısmı veya tamamı ingilizce olan içeriğe işaret edebilir.) Üste | Geri Bildirim Ver Daha fazla bilgi Daha fazla bilgi edinin: (_ PRI ') VE TABLE_SCHEMA = DATABASE () ve TABLE_NAME =' t1 'SIPARIYOR BY ORDINAL_POSITION Veritabanınızı önce bir USE bildirimi yürüterek ya da yalnızca DB'nizin adıyla DATABASE () 'i değiştirmeyi unutmayın. - wisefish


Bunu GROUP_CONCAT ve GROUP BY ile yapabilirsin:

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

(denenmemiş)

DÜZENLE: Dokümanlar, GROUP_CONCAT’ın ilgili GROUP BY’ye ihtiyaç duyduğunu belirtmiyor, bu yüzden şunu deneyin:

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

0
2017-08-23 12:13



Bu sütun başına, bunun için tablo geniş ihtiyacım var her sütun, değerleri olmayan bir dizi sütunla bırakıyor. Orijinal yazımı bir örnekle genişleteceğim. - Sherlock
Düzenlemenize verilen yanıt: Bu, tüm sütunların manuel olarak eklenmesini gerektirir. Zor bir seçenek, ama teşekkürler. - Sherlock
Bu işe yarıyor mu? SELECT w as 'non empty cols',LENGTH(REPLACE(w, ',', '')) FROM (SELECT column_name as w FROM information_schema.columns WHERE table_schema = "my_database" AND table_name = "my_table" ORDER BY table_name,ordinal_position ) t; - Chris K


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

Sadece basit bir sorgu ile bu iki sütunu alacaksınız.


-2
2017-09-18 05:59