Mysql query about creating ddl format table
I am a mysql newbie. I have a question on what to do to create a ddl table. So far I just wrote creating a ddl table like this ...
CREATE TABLE file (
file_id mediumint(10) unsigned NOT NULL AUTO_INCREMENT,
filename varchar(100) NOT NULL,
file_notes varchar(100) DEFAULT NULL,
file_size mediumint(10) DEFAULT NULL,
file_type varchar(40) DEFAULT NULL,
file longblob DEFAULT NULL,
CONSTRAINT pk_file PRIMARY KEY (file_id)
);
But I often see people make their ddl creation table like this ...
CREATE TABLE IF NOT EXISTS `etags` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`item_code` varchar(100) NOT NULL,
`item_description` varchar(500) NOT NULL,
`btn_type` enum('primary','important','success','default','warning') NOT NULL DEFAULT 'default',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
A few questions...
-
What is the difference causing quotes around the table name and column name?
-
Is it good practice to explicitly declare the engine and character set? What are the default engines and character sets?
thank
source to share
-
There is no difference. Identifiers (table names, column names, etc.) must be enclosed in backtabs if they contain special characters or reserved words. Otherwise, backlinks are optional.
-
Yes, this is a good practice for portability to other systems. If you are re-creating the table, having the storage engine and character set explicitly specified in the CREATE TABLE statement means that your statement will not depend on the variable settings
default_character_set
anddefault-storage-engine
(they may be changed or different in another database.)
You can define your DDL definition in the same format using a statement SHOW CREATE TABLE
such as.
SHOW CREATE TABLE `file`
The CREATE TABLE
DDL syntax you see posted by other users is usually in the format that is generated as the output of this statement. Note that MySQL doesn't care if an identifier contains special characters or reserved words (to check if callbacks are needed or not), it just goes and wraps all identifiers backwards.
source to share
-
Countdowns, reserved words and some special characters can be used in names.
This is just a security measure and many tools add them automatically. -
The default mechanism and encoding can be set in the server configuration.
They are often (but not always) set toMyISAM
andlatin1
.
Personally, I find it good practice to define the engine and encoding so you can be sure of what you get.
source to share