Convert entire mysql database to lowercase

Asked

Viewed 568 times

0

It is possible convert all columns of tables in a database mysql for lower-case letters?

I have several records and several tables, all are a patchwork of upper and lower case (and some with accentuation).

Detail: the type is Innodb; collation is defined as utf8_bin.

mysql sql database phpmyadmin

  • update Table SET value = LOWER(value)

  • @Gustavo, I published an answer, see if it helps. I hadn’t seen anything like this yet.. gave a nice job, but I liked the code =]

  • you want to convert the column name or value?

  • @rray the values!

  • @rLinhares didn’t work...

  • @Did Gustavomacielsetta see if there were any errors or if he updated any data? Did he look at the fiddle?

  • @Since the fiddle did not work. And in my bank tbm does not say that there is no table table_tmp. I created the table with the same columns but continued to show error...

  • @The fiddle wasn’t working. But I checked now and he’s okay, but I have no idea how to apply this solution to my bank, I mean, I’m still the same...

  • What’s wrong with the bank? Why can’t you apply??

Show 4 more comments

1 answer

1

I don’t know any "simple" way to do this, but through this algorithm I believe that 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 of each table (one in each row). In the assembly of this commando, I used the CONCAT() for the structure ("update table ...") and GROUP_CONCAT() to take all fields from the table and organize them in a row.
Done that, in a loop just load the commands and run them.

Working example

Browser other questions tagged mysql sql database phpmyadmin

You are not signed in. Login or sign up in order to post.