Você pode ler este post em português também.
Hello!
The April’s post brings 10 stupid tips for day-by-day work with MySQL. Let’s see them:
- Use a
.my.cnf
file in your homedir with user/password data:[client] user=root password=my_p@ss-w0rd
- End your commands with
G
instead of";"
: This will bring a “form” view instead the normal tabular view. This is necessary a lot of times; - Materialized views: When the regular views (created with CREATE VIEW) doesn’t offer the expected performance, use triggers to feed tables that could work like the regular views. Being carefull, it helps a lot!
- Take care of the parameter
ENGINE
of your databases and tables: Choose the best considering the R/W relation of the tables; - Adjust your MySQL prompt to your best fit at your
.my.cnf
in your homedir. The example below shows"mysql:db_name>"
. There are also theu
(to show the username) andh
(to show the hostname):prompt='mysql:d>'
- At your mysql prompt,
CTRL+A
brings you to the begin of the line.CTRL+E
brings you to the end; - To create a t2 table with the same structure of t1 table:
CREATE TABLE t2 LIKE t1;
- To create a user and give its access in one line:
GRANT ALL ON db_name.* TO 'username'@'host' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;
- To generate a CSV file from the shell (better than
OUTFILE
’cause this below shows the column names):mysql -AD dbname -e "Your SELECT here;" | tr "t" ";" > resultset.csv
And the last, but not least (stupid). To show the queries in execution (the FULL
parameter is optional):
SHOW [FULL] PROCESSLIST;
Memorize them and your day-by-day with MySQL will be easier.
See you!
One thought on “10 stupid tips for MySQL”