Today I Learned

A Hashrocket project

Show Create Statement For A Table In MySQL

In MySQL, you can get a quick rundown of a table using describe users. An alternative to this approach is to have MySQL show the create statement for a table.

> show create table users\G
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(80) NOT NULL,
  `last_name` varchar(80) NOT NULL,
  `email` varchar(80) NOT NULL,
  `middle_initial` varchar(80) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

This includes some additional information like primary key and index information. It is also a great way to study the SQL that it takes to create all the facets of a table.

See the show create table docs for more details.

h/t Jake Worth

Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, a series of free weekly PostgreSQL screencasts.