MySQL Cheat Sheet

From Shrubbery

Jump to: navigation, search


MySQL things that I always forget the syntax for.

Contents

Rename a Column

See ALTER TABLE Syntax

alter table {table_name} change {old_col_name} {new_col_name} [col_definition]

Change column type

See ALTER TABLE Syntax

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

See CREATE TABLE Syntax

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);

See PURGE MASTER LOGS syntax

See Also

Personal tools