wiki:mysql_miscellaneous_commands
This is an old revision of the document!
Table of Contents
mysql miscellaneous commands
Create user and grant rights
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,REFERENCES,ALTER,INDEX on db_name.* TO 'user'@'localhost' IDENTIFIED BY 'password' FLUSH PRIVILEGES;
Show grants for user
SHOW GRANTS FOR 'user'@'localhost';
Check mysql table collation
USE db_name; SELECT DISTINCT C.collation_name, T.table_name, T.table_schema FROM information_schema.tables AS T, information_schema.`collation_character_set_applicability` AS C WHERE C.collation_name = T.table_collation AND T.table_schema = DATABASE();
Change collation and charset for one table
ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
Change collation and charset for tables
This outputs list of queries to apply, so pipe it into sql file then run it.
SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;') FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'db' AND TABLE_TYPE = 'BASE TABLE';
Command (delete first row in convert.sql, it contains sql statement)
mysql -p db2 < make_queries_for_utf8_conversion.sql > convert.sql
then run
mysql -p db2 < convert.sql
Change collation and charset for columns:
SELECT CONCAT('ALTER TABLE ', C.TABLE_NAME, ' CHANGE ', C.COLUMN_NAME, ' ', C.COLUMN_NAME, ' ', C.COLUMN_TYPE, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;') AS queries FROM information_schema.COLUMNS AS C LEFT JOIN information_schema.TABLES AS T ON C.TABLE_NAME = T.TABLE_NAME WHERE C.COLLATION_NAME IS NOT NULL AND C.TABLE_SCHEMA='db' AND T.TABLE_TYPE='BASE TABLE';
Tested on
See also
References
wiki/mysql_miscellaneous_commands.1619707640.txt.gz · Last modified: 2021/04/29 14:47 by antisa