You might need INFORMATION_SCHEMA.KEY_COLUMN_USAGE
You would have to issue this query
SELECT column_name
FROM information_schema.key_column_usage
WHERE table_schema='$db'
AND table_name='$table'
AND constraint_name='PRIMARY';
This will retrieve every column in the PRIMARY KEY
.
You could then make this query generate the ALTER TABLE
clauses for you as follows:
SET group_concat_max_len = 1048576;
SELECT GROUP_CONCAT(CONCAT('MODIFY COLUMN ',column_name,' DEFAULT NULL'))
FROM information_schema.key_column_usage
WHERE table_schema='$db'
AND table_name='$table'
AND constraint_name='PRIMARY';
This will produce a comma-separated list of MODIFY COLUMN
clauses you need to apply to the column after dropping the PRIMARY KEY
. If you want to drop any of these columns, you have to change MODIFY COLUMN
to DROP COLUMN
for the columns that need to be dropped.
Give it a Try !!!
UPDATE 2014-12-09 14:00 EST
Your comment
I'm trying to understand how this works. GROUP_CONCAT(CONCAT('MODIFY COLUMN ',column_name,' DEFAULT NULL')) is especially confusing to me. Where does column_name come from in this query? I tried running it as is in MySQL, but I get no results
To give you a general idea of how query can help you, here is a table in MySQL on my laptop
mysql> show create table weird.vinner\G
*************************** 1. row ***************************
Table: vinner
Create Table: CREATE TABLE `vinner` (
`startnr` int(11) NOT NULL,
`alder` varchar(25) NOT NULL,
`kjonn` varchar(25) NOT NULL,
`ovelseid` varchar(25) NOT NULL,
`slutttid` int(11) NOT NULL,
PRIMARY KEY (`startnr`,`slutttid`),
KEY `slutttid` (`slutttid`),
CONSTRAINT `vinner_ibfk_1` FOREIGN KEY (`slutttid`) REFERENCES `passering` (`slutttid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
See the PRIMARY KEY. It has two columns: startnr
and slutttid
.
Now, if I run my code on that tabl, I get this list
mysql> SET group_concat_max_len = 1048576;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT GROUP_CONCAT(CONCAT('MODIFY COLUMN ',column_name,' DEFAULT NULL'))
-> FROM information_schema.key_column_usage
-> WHERE table_schema='weird'
-> AND table_name='vinner'
-> AND constraint_name='PRIMARY';
+------------------------------------------------------------------------+
| GROUP_CONCAT(CONCAT('MODIFY COLUMN ',column_name,' DEFAULT NULL')) |
+------------------------------------------------------------------------+
| MODIFY COLUMN startnr DEFAULT NULL,MODIFY COLUMN slutttid DEFAULT NULL |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
The output creates a MODIFY COLUMN clause for both columns.
If you were dropping the slutttid column, you would use PHP to change MODIFY
to DROP
MODIFY COLUMN startnr DEFAULT NULL,DROP COLUMN slutttid
You would be doing this kind of code to generate the clauses needed
I'd use DENSE_RANK() to match values from distinct rows.
And MAX() OVER() to detemine which column has the biggest number of values (only for the ORDER BY purposes, you can skip this if the order doesn't matter).
Here is a working example:
create table test2 (
colone char(1), coltwo char(1), colthree char(1));
insert into test2 values
('a','d','j'),
('a','e','k'),
('a','e','m'),
('b','e','o'),
('c','e','o'),
('d','e','o'),
('f','e','o')
;
WITH cte AS(
SELECT
colone, DENSE_RANK () OVER (ORDER BY colone) as c1rn,
coltwo, DENSE_RANK () OVER (ORDER BY coltwo) as c2rn,
colthree, DENSE_RANK () OVER (ORDER BY colthree) as c3rn
FROM test2
) , cte2 as (
SELECT
t1.colone, t2.coltwo, t3.colthree,
MAX(t1.c1rn) OVER() as max_c1rn,
MAX(t2.c2rn) OVER() as max_c2rn,
MAX(t3.c3rn) OVER() as max_c3rn
FROM cte t1
FULL OUTER JOIN
cte t2 ON t1.c1rn = t2.c2rn
FULL OUTER JOIN
cte t3 ON
t1.c1rn = t3.c3rn
OR
t2.c2rn = t3.c3rn
)
SELECT
colone, coltwo, colthree
FROM cte2
GROUP BY colone, coltwo, colthree
ORDER BY
CASE WHEN colone IS NULL THEN 1 ELSE 0 END, colone,
CASE WHEN coltwo IS NULL THEN 1 ELSE 0 END, coltwo,
CASE WHEN colthree IS NULL THEN 1 ELSE 0 END, colthree
;
Hopefully your table is not very big, it will probably be scanned as many times as many columns you have, so it may take quite a lot of time.
Best Answer
I have a Dynamic SQL solution
PROPOSED QUERY
EXAMPLE
Let's use a sample table I made up for another question
mysql>
PROPOSED QUERY DISPLAYED
PROPOSED QUERY EXECUTED
PROPOSED QUERY EXECUTED WITH NULL DATA
Let's change two rows to have NULL column data
Let's run it again
GIVE IT A TRY !!!
UPDATE 2014-12-29 15:25 EST
In response to your last comment, use this version
You do not need to check the datatype with this one.
To show you that it works ...