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 .
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 .
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.