Export the result of a SELECT statement as CSV data
With SELECT ~ INTO OUTFILE output.csv, the file /tmp/hoge.csv is written on the DB server. Be careful when the server running the command differs from the DB server.
1 2
use dbname SELECT * INTO OUTFILE'/tmp/hoge.csv' FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '' LINES STARTING BY '' TERMINATED BY '\r\n' FROM table;
Load a SQL file from the command line and save the execution result as CSV
1
mysql -h <host> -u <user> -p<password> <db_name> -e "`cat query.sql`" | sed -e 's/\t/,/g' >/tmp/result.csv
mysql -u user -ppassword > use dbname > LOAD DATA INFILE "<CSVFile>" INTO TABLE table FIELDS TERMINATED BY ',' ENCLOSED BY '"';
TRUNCATE all tables
1
mysql -u root dbname -N -e 'show tables' | whileread table; do mysql -u root -e "truncate table $table" dbname; done
Check the AUTO_INCREMENT value
1
SELECT auto_increment FROM information_schema.tables WHERE table_name = '<table>';
Set the AUTO_INCREMENT value
1
ALTER TABLE <table> auto_increment=<int val>;
Rename a table
1
ALTER TABLE <old table> rename <new table>;
Add a column to a table
1
ALTER TABLE <table> ADD <column> TINYINT(3) NOT NULL DEFAULT <deafult value> COMMENT '<comment>' AFTER <previous column>;
Example) Add a column named mailmagazine_status after the email column of the user table, as tinyint(3) unsigned with a default of 0
1
ALTER TABLE user ADD mailmagazine_status TINYINT(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'メルマガステータス' AFTER email;
Drop a column from a table
1
ALTER TABLE <table> DROP COLUMN <column>;
Modify a column of a table
1
ALTER TABLE <table> CHANGE <old column> <new column> <column 定義>;
Example) In the product table, rename the column “no” to “id”, and change it to unsigned, NOT NULL, default 0, and column comment “商品ID”
1
ALTER TABLE product CHANGE `no` `id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '商品ID';
List indexes
1
SHOW INDEXES FROM <table>;
Add an index
1 2 3
ALTER TABLE <table> ADD INDEX <index name>(<column>);
ALTER TABLE <table> ADD INDEX <index name>(<column1>,<column2>);
Drop an index
1
ALTER TABLE <table> DROP INDEX <index name>;
Add a unique constraint
1
ALTER TABLE <table> ADD UNIQUE(<column>);
Drop a unique constraint
Check the DB character set
1
SHOW CREATE DATABASE dbname
Run a gzip-compressed file against a specific DB schema
1
zcat dump.sql.gz | mysql -u <user> -p<password> dbname
List statistics for all tables, ordered by size
1
SELECT table_name, engine, table_rows AS tbl_rows, avg_row_length AS rlen, floor((data_length + index_length) / 1024 / 1024) AS allMB, floor((data_length) / 1024 / 1024) AS dMB, floor((index_length) / 1024 / 1024) AS iMB FROM information_schema.tables WHERE table_schema = database() ORDER BY (data_length + index_length) DESC;
Check a table’s character set and so on
1 2 3 4 5 6 7
SELECT * FROM information_schema.schemata WHERE schema_name = 'database_name';
CREATE DATABASE `database_name` CHARACTER SET utf8;
Set a user, password, and privileges for a specific DB/host
You need to set the following:
database_name
user_name
host_name
password
1
GRANT ALL PRIVILEGES ON `database_name`.* TO `user_name`@'host_name' IDENTIFIED BY 'password' WITH GRANT OPTION;
Grant a specific user permission to view the MySQL slow query log
1
GRANT select ON mysql.slow_log TO user_name;
Grant a specific user permission to view the MySQL slow query log on RDS (AWS)
1
GRANT EXECUTE ON PROCEDURE mysql.rds_rotate_slow_log TO user_name;
Show a specific user’s privileges
1
SHOW GRANTS for 'user_name'@'%';
Apply the settings
After setting the privileges above, apply the settings.
1
FLUSH PRIVILEGES;
When granting privileges via GRANT, FLUSH PRIVILEGES is not strictly required, but it’s good to run it just in case. When you grant privileges via INSERT, UPDATE, DELETE, and so on, FLUSH PRIVILEGES is required.
