CODEWITHSUNDEEP
c#mysql
AndyDB
AndyDB

Reputation: 431

Explanation of MySQL query additions

I am writing a function within my C# app that in this instance is creating a new table in a MySQL database. However when I edit the table structure (after creation), I see three lines that were not added via the code.

Do I need to double check any of these, or should them be OK?

AUTO_INCREMENT = 1
CHARACTER SET latin1
COLLATE latin1_swedish_ci;

Syntax used:

CREATE TABLE egr.test ( machine_id int NOT NULL AUTO_INCREMENT, machine_desc varchar(64) DEFAULT NULL, product_family varchar(64) DEFAULT NULL, factory varchar(64) DEFAULT NULL, model varchar(64) DEFAULT NULL, size varchar(64) DEFAULT NULL, PRIMARY KEY (machine_id)) ENGINE = INNODB;

Upvotes: 0

Views: 74

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

There is no issue with the code. When you create a table as below 

CREATE TABLE test ( 
machine_id int NOT NULL AUTO_INCREMENT, 
machine_desc varchar(64) DEFAULT NULL, 
product_family varchar(64) DEFAULT NULL, 
factory varchar(64) DEFAULT NULL, 
model varchar(64) DEFAULT NULL, 
size varchar(64) DEFAULT NULL, 
PRIMARY KEY (machine_id)
) ENGINE = INNODB ;

and then use the command show create table test

You will get to see something as

| test  | CREATE TABLE `test` (
  `machine_id` int(11) NOT NULL AUTO_INCREMENT,
  `machine_desc` varchar(64) DEFAULT NULL,
  `product_family` varchar(64) DEFAULT NULL,
  `factory` varchar(64) DEFAULT NULL,
  `model` varchar(64) DEFAULT NULL,
  `size` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`machine_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

Now why AUTO_INCREMENT = 1 is not shown, since if its set to 1 at the beginning then its not shown and it means that its set to 1 which is implied, however if you create the same above table as

CREATE TABLE test ( 
machine_id int NOT NULL AUTO_INCREMENT, 
machine_desc varchar(64) DEFAULT NULL, 
product_family varchar(64) DEFAULT NULL, 
factory varchar(64) DEFAULT NULL, 
model varchar(64) DEFAULT NULL, 
size varchar(64) DEFAULT NULL, 
PRIMARY KEY (machine_id)
) ENGINE = INNODB AUTO_INCREMENT = 2 ;

And then show create table test it will display as

| test  | CREATE TABLE `test` (
  `machine_id` int(11) NOT NULL AUTO_INCREMENT,
  `machine_desc` varchar(64) DEFAULT NULL,
  `product_family` varchar(64) DEFAULT NULL,
  `factory` varchar(64) DEFAULT NULL,
  `model` varchar(64) DEFAULT NULL,
  `size` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`machine_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 |

Since the primary key is not longer 1 and the table description will show you where the last id of the auto_incremented value is.

If you need to know the other details of the tables in the database you may need to use the following command as

show table status from your_db_name \G

And you may get something as

mysql> show table status from test \G
*************************** 1. row ***************************
           Name: test
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 12582912
 Auto_increment: 2
    Create_time: 2014-05-23 16:39:08
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment:

Upvotes: 1

Related Questions