MySQL Cheat Sheet
From Shrubbery
- MySQL things that I always forget the syntax for.
Contents |
Rename a Column
alter table {table_name} change {old_col_name} {new_col_name} [col_definition]
Change column type
alter table {table_name} modify {col_name} {new_col_definition}
or
alter table {table_name} change {col_name} {col_name} {new_col_definition}
Adding a 'milliseconds' column
If you have a table that has a TIMESTAMP column, and you want to add milliseconds (e.g. Java System.currentTimeMillis()) you might need to back-populate the new column for the existing rows. The UNIX_TIMESTAMP function will do the job of converting the TIMESTAMP values into 'millis since the epoch'.
alter table {table_name} add column ({col_name} BIGINT(20));
update {table_name} set {col_name} = UNIX_TIMESTAMP({old_col_name}) * 1000;
Now, a not-null constraint can be added if necessary:
alter table {table_name} modify {col_name} BIGINT(20) not null;
Creating Tables
Create a table with the same columns as another table
CREATE TABLE {new_table} LIKE {existing_table};
This also works to create tables in another database:
CREATE TABLE my_clone LIKE otherdb.the_original;
Rename a Table
alter table {old_name} rename {new_name};
Foreign Key Constraints
Defining FK constraints in the CREATE TABLE statement:
create table {table_name} ( {column_def} [ , {column_def} ] ...
primary key ( {primary_key_column(s)} ),
constraint {constraint_name} foreign key {index_ame}
( {fk_col_name} ) references {ref_table_name} ( {ref_col_name} )
);
Defining FK constraints after the fact:
alter table {table_name}
add constraint {constraint_name} foreign key {index_name}
( {fk_col_name} ) references {ref_table_name} ( {ref_col_name} );
Drop a foriegn key constraint:
alter table {table_name}
drop foreign key {index_name}
Typically, you will want to drop constraints before doing any bulk loading operations on the database, or before dropping the columns involved in the FK constraint.
Grant Permissions
Allow an app server full control over it's database:
grant all privileges on {appdbname}.* to '{appuser}'@'{apphost}' identified by {apppassword}
- {appdbname} is the database the application will use.
- {appuser} and {apppassword} are the username and password the application server will use to connect to the database.
- {apphost} is typically localhost when the app server and the db server are running on the same machine.
- {apphost} should be a subnet expression when the app server is running on a different host, e.g. 192.168.0.%.
Remember: The wildcard character for network addresses in MySQL is %, not * as you might expect.
Functions and Queries
Start of day / end of day timestamps
Use the timestamp(), date(), and addtime() functions together to get start of day and end of day:
- start of day
- timestamp(date(<timestamp expression>))
- end of day
- addtime(timestamp(date(<timestamp expression>)),'23:59:59')
Given a table with a TIMESTAMP column called 'ts'.
select e.timestamp, timestamp(date(e.ts)) as start_of_day, addtime(timestamp(date(e.ts)),'23:59:59') as end_of_day from event e;
Meta Data and Performance Analysis
What are the biggest tables?
You can use the 'information_schema' database to find out which tables are the largest:
select
TABLE_SCHEMA,
TABLE_NAME,
concat(round(table_rows/1000000,2),'M') rows,
concat(round(data_length/(1024*1024*1024),2),'G') DATA,
concat(round(index_length/(1024*1024*1024),2),'G') idx,
concat(round((data_length+index_length)/(1024*1024*1024),2),'G') total_size,
round(index_length/data_length,2) idxfrac
from information_schema.TABLES s
order by data_length+index_length desc;
See http://www.mysqlperformanceblog.com/2008/03/17/researching-your-mysql-table-sizes
Purge old binlogs
You can also purge logs more than 7 days old:
PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 7 DAY);
