Mysql – SQL query to get the syntax of an existing table

MySQLsyntax

Is there any SQL query to get the syntax of an existing table in my MySql database?

Intent:
Iam in the early stages of working with databases and I am using the MySql workbench for creating tables and updating them whenever needed. At some point there are cases where I would like to have the syntax that the table is now holding so as to document it or paste it in forums like StackOverflow(expecting that I can show all the constraints in fact the foreign key constraints on the table in an easy way).

Please let me know if there is any workaround for this.

Best Answer

You can use SHOW CREATE TABLE <table_name>; to get CREATE TABLE statement. Eg. SHOW CREATE TABLE acl_user_role; This will show you the table name and CREATE TABLE statement, something like:

CREATE TABLE `acl_user_role` (
  `UserId` INT(10) UNSIGNED NOT NULL,
  `RoleId` SMALLINT(5) UNSIGNED NOT NULL,
  PRIMARY KEY (`UserId`,`RoleId`),
  KEY `FK_UserRole_Role` (`RoleId`),
  CONSTRAINT `FK_UserRole_Role` FOREIGN KEY (`RoleId`) REFERENCES `acl_role` (`RoleId`),
  CONSTRAINT `FK_UserRole_User` FOREIGN KEY (`UserId`) REFERENCES `user` (`UserId`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

If you want to know about Keys for a particular table, use something like SHOW KEYS FROM acl_user_role;

And if you want to get all keys and constraints referencing to other table (like foreign key references), use:

SELECT * FROM `information_schema`.`KEY_COLUMN_USAGE`
WHERE TABLE_NAME = '<your_table_name>' AND TABLE_SCHEMA = '<your_database_name>';

-- Example
SELECT * FROM `information_schema`.`KEY_COLUMN_USAGE`
WHERE TABLE_NAME = 'acl_user_role' AND TABLE_SCHEMA = 'db_shop';

Hope this may help you...

Related Solutions

MySQL Unique Constraint – Handling Large Columns

You do not want a gigantic gen_clust_index (Internal Clustered Index). That size is ungodly huge even for a secondary index.

You may have to resort to triggers or stored procedures to check for the key well in advance.

You could also think about performing an SHA1 function call using the VARCHAR(3071) field. SHA1 will return a 40-character field. This hash may be just what you need to index.

Suppose you have this

CREATE TABLE mytable
(
    id int not null auto_increment,
    txt VARCHAR(3071),
    primary key (id)
) ENGINE=InnODB;

and you want to make a UNIQUE index on txt. Try the SHA1 approach

CREATE TABLE mytablenew LIKE mytable;
ALTER TABLE mytable ADD txtsha1 CHAR(40);
ALTER TABLE mytable ADD UNIQUE KEY (txtsha1);
INSERT INTO mytablenew (id,txt,txtsha1)
SELECT id,txt,SHA1(txt) FROM mytable;

Then, count them

SELECT COUNT(1) FROM mytable;
SELECT COUNT(1) FROM mytablenew;

If the Counts are the Same, CONGRATULATIONS !!! Now you have a unique index of length 40. You can finish up with:

ALTER TABLE mytable RENAME mytableold;
ALTER TABLE mytablenew RENAME mytable;
DROP TABLE mytableold;

This could be more atomically as pointed out in the comments below:

RENAME TABLE mytable TO mytableold, mytablenew TO mytable;
DROP TABLE mytableold;

Perform this on whatever table you intend to have this big column. You have to remember to add the SHA1 of the data along with the data upon INSERT.

The odds of duplicate keys is 1 in 2 to the 160th power (that 1.4615016373309029182036848327163e+48. If I get the exact figure, I'll post it someday).

Give it a Try !!!

Mysql – How to push data from the local MAMP database to the remote database

There are a couple of ways to get your data from your local Dev to your remote Dev or Production environment. The first and simplest way is to just dump your db and upload it. You can also use a tool like phpMyAdmin to Sync your db but that would require you to install more software and understand basic networking. If you have a huge Dev database with lots of unused test data you can also dump and import just the db structure which will give you an empty db with no information (users, setting, whatever you are storing). I would highly recommend installing phpMyAdmin on your local Dev server to simplify your life, it makes any of these options extremely simple and pretty much 1 click tasks.

Best Answer

Related Solutions

MySQL Unique Constraint – Handling Large Columns

Mysql – How to push data from the local MAMP database to the remote database

Related Question