Convert all mysql bank to lowercase

0
Is it possible to convert all columns of tables in a mysql database to lowercase letters?

I have several records and several tables, all of them are a miscellaneous case (and some with emphasis).

Detail: the type is InnoDB ; collation is set to utf8_bin .

    
asked by anonymous 04.10.2018 / 00:12

1 answer

1

I do not know any "simple" way to do this, but through this algorithm I believe it will have the expected result:

SET @row_number := 0;

INSERT INTO tabela_tmp
SELECT (@row_number:=@row_number + 1) AS num, CONCAT('update ', TABLE_NAME, ' SET ', CONCAT(GROUP_CONCAT(CONCAT(COLUMN_NAME, '=LOWER(', COLUMN_NAME, ')')), ';'))
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME;

SET @id_tabela:=1, @total:=(SELECT COUNT(*) FROM tabela_tmp);

WHILE @id_tabela <= @total DO
    SET @comando = (SELECT comando FROM tabela_tmp where num = @id_tabela);

    PREPARE myquery FROM @comando;
    EXECUTE myquery;

    SET @id_tabela = @id_tabela + 1;    
END WHILE;

tabela_tmp is a table that will store the update commands for each table (one on each row). In assembling this command , I used CONCAT() for the structure (" update table ... ") and GROUP_CONCAT() to grab all table fields and organize them in a row.
Then, in a loop just load the commands and execute them.

Example working

    
04.10.2018 / 16:51