MySQL sorting table by column names

Question

I have already built a table with field names in arbitrary order. I want those field names to be in alphabetical order so that I can use them in my dropdown list. Is it possible with a query?

Nonym · Accepted Answer

Note: The following code will alter the specified table and reorder the columns in alphabetical order

This should do the trick. It's a bit messy and lengthy, and you'll have to change the database name and table name, but for this one, the only requirement is that there is a database named "test" and that you are running these commands in it:

Let's create the tables we need:

-- CREATE TESTING TABLE IN A DATABASE NAMED "test"
DROP TABLE IF EXISTS alphabet;
CREATE TABLE alphabet (
      d varchar(10) default 'dee' not null
    , f varchar(21)
    , e tinyint
    , b int NOT NULL
    , a varchar(1)
    , c int default '3'
);

-- USE A COMMAND STORAGE TABLE
DROP TABLE IF EXISTS loadcommands;
CREATE TABLE loadcommands (
      id INT NOT NULL AUTO_INCREMENT
    , sqlcmd VARCHAR(1000)
    , PRIMARY KEY (id)
);

Now let's create the two stored procedures required for this to work:

Separating them since one will be responsible for loading the commands, and including a cursor to immediately work with it isn't plausible (at least for me and my mysql version):

-- PROCEDURE TO LOAD COMMANDS FOR REORDERING
DELIMITER //
CREATE PROCEDURE reorder_loadcommands ()
BEGIN
    DECLARE limitoffset INT;
    SET @rank = 0;
    SET @rankmain = 0;
    SET @rankalter = 0;
    SELECT COUNT(column_name) INTO limitoffset 
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE table_schema = 'test'
        AND table_name = 'alphabet';

    INSERT INTO loadcommands (sqlcmd)
    SELECT CONCAT(t1.cmd, t2.position) AS commander FROM (
        SELECT @rankalter:=@rankalter+1 AS rankalter, CONCAT('ALTER TABLE '
            , table_name, ' '
            , 'MODIFY COLUMN ', column_name, ' '
            , column_type, ' '
            , CASE 
                WHEN character_set_name IS NOT NULL 
                    THEN CONCAT('CHARACTER SET ', character_set_name, ' COLLATE ', collation_name, ' ')
                ELSE ' '
              END
            , CASE 
                WHEN is_nullable = 'NO' AND column_default IS NULL 
                    THEN 'NOT NULL '
                WHEN is_nullable = 'NO' AND column_default IS NOT NULL 
                    THEN CONCAT('DEFAULT \'', column_default, '\' NOT NULL ')
                WHEN is_nullable = 'YES' THEN 'DEFAULT NULL '
              END
            ) AS cmd
            , column_name AS columnname
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE table_schema = 'test'
        AND table_name = 'alphabet'
        ORDER BY columnname
    ) t1
    INNER JOIN (
        SELECT @rankmain:=@rankmain+1 AS rownum, position FROM (
            SELECT 0 AS rownum, 'FIRST' AS position
                , '' AS columnname
            UNION
            SELECT @rank:=@rank+1 AS rownum, CONCAT('AFTER ', column_name) AS position
                , column_name AS columnname
            FROM INFORMATION_SCHEMA.COLUMNS
                WHERE table_schema = 'test'
                AND table_name = 'alphabet'
            ORDER BY columnname
            LIMIT limitoffset
        ) inner_table
    ) t2 ON t1.rankalter = t2.rownum

    ;

END//
DELIMITER ;

If anyone thinks/sees that I'm missing to include any important column attributes in the ALTER command, please hesitate not and mention it! Now to the next procedure. This one just executes the commands following the order of column id from the loadcommands table. :

-- PROCEDURE TO RUN EACH REORDERING COMMAND
DELIMITER //
CREATE PROCEDURE reorder_executecommands ()
BEGIN
    DECLARE sqlcommand VARCHAR(1000);
    DECLARE isdone INT DEFAULT FALSE;

    DECLARE reorderCursor CURSOR FOR
    SELECT sqlcmd FROM loadcommands ORDER BY id;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET isdone = TRUE;

    OPEN reorderCursor;
    read_loop:LOOP
        FETCH reorderCursor INTO sqlcommand;

        IF isdone THEN
            LEAVE read_loop;
        END IF;

        SET @sqlcmd = sqlcommand;
        PREPARE stmt FROM @sqlcmd;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;          

    END LOOP read_loop;

    CLOSE reorderCursor;    
END//
DELIMITER ;

The SQL is long, so if someone can point out ways (and has tested them) to make this shorter I'd gladly do it, but for now, this at least works on my end. I also didn't need to put dummy data in the alphabet table. Checking the results can be done using the SHOW... command.

The last part:

-- TO TEST; AFTER RUNNING DDL COMMANDS:

SHOW CREATE TABLE alphabet;     -- SEE ORIGINAL ORDER
CALL reorder_loadcommands();    -- PREPARE COMMANDS
CALL reorder_executecommands(); -- RUN COMMANDS
SHOW CREATE TABLE alphabet;     -- SEE NEW ORDER

Perhaps later on I could make reorder_loadcommands dynamic and accept table and schema parameters, but I guess this is all for now..

MySQL sorting table by column names

Answers (2)

Note: The following code will alter the specified table and reorder the columns in alphabetical order

Related Questions