[MySQL] Creating a table using the CREATE TABLE statement


Writing time : 2014-06-27 18:41:20

Creating a table using the CREATE TABLE statement

Sample CREATE TABLE statement


Let's create the students table to describe the CREATE TABLE statement.

The students table is a table to store the grade, class, number, name, and gender of students.

As it is a table for explanation, there are some unfamiliar keywords and some parts that have nothing to do with practicality.

CREATE TABLE `students` (  
`idx` INT(11) NOT NULL auto_increment,  
`grade` TINYINT(4) ZEROFILL NOT NULL,  
`class` TINYINT(4) ZEROFILL NOT NULL,  
`number` TINYINT(4) UNSIGNED NOT NULL,  
`student_name` VARCHAR(16) NOT NULL,  
`gender` ENUM('M', 'F') NOT NULL DEFAULT 'M',  
`insert_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,  
PRIMARY KEY  (`idx`),  
INDEX idx_name (`student_name`),  
UNIQUE INDEX idx_class_info(`grade`, `class`, `number`)  
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8;  

Starting from the relatively simple part, it is as follows.

NOT NULL

`idx` INT(11) NOT NULL auto_increment,  

This means that the field does not allow NULL values.
An error occurs if NULL is specified in the relevant field in the insert or update statement.

auto_increment

`idx` INT(11) NOT NULL auto_increment,  

This means that it is a field that automatically increases its value when a record is added.

Since AUTO_INCREMENT=100 is specified, the value increases from 100.

VARCHAR(16)

`student_name` VARCHAR(16) NOT NULL,  

VARCHAR means a field used as a variable storage.
Since the size is set to 16, it can store up to 16 characters.

ENUM('M','F')

`gender` ENUM('M', 'F') NOT NULL DEFAULT 'M',  

You can select from'M' or'F' to enter a value.

DEFUALT

`gender` ENUM('M', 'F') NOT NULL DEFAULT 'M',  

Specifies the default value to be set when no value is specified in the INSERT statement.

DEFAULT CURRENT_TIMESTAMP

`insert_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,  

Default value is specified in the TIMESTAMP format field and the current time is set.

INSERT


Gender and insert_date fields have default values, and idx is set to increase automatically, so only grade, class, number, and student_name are set to execute INSERT.

-- 1학년 2반 1번 학생 (이름은 김수현이고 성별은 정의하지 않았기 때문에 DEFAULT값인 M이 저장됨)  
INSERT INTO students(grade, class, number, student_name) VALUES(1, 2, 1, '김수현');  
  
-- 1학년 2반 2번 학생 (이름은 전지현이고 성별을 F로 설정)  
INSERT INTO students(grade, class, number, student_name, gender) VALUES(1, 2, 2, '전지현', 'F');  


-- INSERT된 내용을 출력  
SELECT * FROM students;  
+-----+-------+-------+--------+--------------+--------+---------------------+  
| idx | grade | class | number | student_name | gender | insert_date         |  
+-----+-------+-------+--------+--------------+--------+---------------------+  
| 152 |  0001 |  0002 |      1 | 김수현        | M      | 2014-06-27 22:51:56 |  
| 153 |  0001 |  0002 |      2 | 전지현        | F      | 2014-06-27 22:51:56 |  
+-----+-------+-------+--------+--------------+--------+---------------------+  

If you print it with the SELECT statement, you can see that the data is well entered. There is a special 0 in front of grade and class, which will be mentioned later.

Range of int


The next part to be mentioned is the idx, grade, calss, and numer fields defined as INT type. Although they are the same INT type, you can see that they are defined differently as INT and TINYINT.

First, http://dev.mysql.com/doc/refman/5.0/en/integer-types.html.

Type Storage
(Bytes)
Minimum Value
(Signed/Unsigned)
Maximum Value
(Signed/Unsigned)
TINYINT 1 -128 127
0 255
SMALLINT 2 -32768 32767
0 65535
MEDIUMINT 3 -8388608 8388607
0 16777215
INT 4 -2147483648 2147483647
0 4294967295
BIGINT 8 -9223372036854775808 9223372036854775807
0 18446744073709551615


Since the idx field is of INT type, it can store from -2147483648 to 2147483647. In other words, it means that there is no problem with storing about 2.1 billion students.

Since grade, class, and number are TINYINT types, they can be stored from -128 to 127 and declared as TINYINT on the premise that class, class, and number do not exceed 127.

By limiting the range, you can get the advantage of using less memory and storage space.

On the other hand, the processing speed may be slightly lowered, and the reason is that the CPU is optimized for INT calculation, and it is not a part to worry about, so let's just refer to it.

For more information, refer to Use int instead of char and short.

ZEROFILL


Going back to the main point, the idx field in the CREATE TABLE statement is defined as INT(11), but I didn't know what (11) means until recently.
I think it means allocating 11 bytes, but it seems like it's not..

In conclusion, part (11) has nothing to do with the size or configurable range.
The INT type is 4 bytes regardless of the number in parentheses, and the range is -2147483648 ~ 2147483647 as mentioned in the range table of INT.

What the parentheses mean is not the storage range, but that when outputting the field set as ZEROFILL set in grade and class, it is filled with 0 as much as the designated place.

Looking at the result of outputting the INSERT contents above, it was output as 0001, 0002 in the case of the 1st grade 2nd class. This is because the grade and class were set to TINYINT(4) in the CREATE TABLE statement.

TINYINT(4) That is, it is set to 4 digits, and the blank value on the left is filled with 0. If you don't remember, take a look at some of the CREATE TABLE below.

`idx` INT(11) NOT NULL auto_increment,  
`grade` TINYINT(4) ZEROFILL NOT NULL,  
`class` TINYINT(4) ZEROFILL NOT NULL,  
`number` TINYINT(4) UNSIGNED NOT NULL,  

If you mention a few more about ZEROFILL, you can see that 0 is not added to the output result of idx because it is set to INT(11) but not ZEROFILL.

`idx` INT(11) NOT NULL auto_increment,  

In other words, the number in parentheses to the right of int has no meaning unless ZEROFILL is set, and it is said that it is customarily added when searching the Internet.

It is not fixed, but in the example I saw, the number in parentheses is the maximum number of digits + 1, for example, for INT, the maximum value is 2147483647 (10 digits) plus 1 (11), and for TINYINT, the maximum value is 127 (3 digits). In many cases, it was indicated by plus (4).

Again, the numbers in parentheses are not fixed rules, so please refer to them only.

UNSIGNED


Another part of the reporter's mention is the UNSIGNED keyword used in the number field. If it is set to UNSIGNED as shown in the range table of INT, negative values cannot be stored, but the range of positive values can be approximately doubled.

These days, the number of students in the Korean class is not large, but if the number of students in the Korean class is 200, it will be possible to process data without error while using 1-byte storage using TINYINT with the UNSIGNED attribute.

For reference, the following is the result when the inputter accidentally enters the half and the number as 200.

INSERT INTO students(grade, class, number, student_name, gender) VALUES(1, 200, 200, '천송이', 'F');  
SELECT * FROM students;  
+-----+-------+-------+--------+--------------+--------+---------------------+  
| idx | grade | class | number | student_name | gender | insert_date         |  
+-----+-------+-------+--------+--------------+--------+---------------------+  
| 152 |  0001 |  0002 |      1 | 김수현       | M      | 2014-06-27 22:51:56 |  
| 153 |  0001 |  0002 |      2 | 전지현       | F      | 2014-06-27 22:51:56 |  
| 154 |  0001 |  0200 |    200 | 천송이       | F      | 2014-06-27 23:14:27 |  
+-----+-------+-------+--------+--------------+--------+---------------------+  

As for the number set as UNSIGNED, 200 entered normally, and 200 is normally entered even for classes that are not set as UNSIGNED unexpectedly. This is because UNSIGNED properties are also applied when ZEROFILL is set.

If ZEROFILL is removed, the value will be set to 127, which is the maximum value of TINYINT.

index


The next thing to mention is the index-related part.

There are three indexes added to the students table: PRIMARY KEY, INDEX, and UNIQUE INDEX, and the set index information is as follows.

PRIMARY KEY

PRIMARY KEY  (`idx`)  

Set the idx field as the primary key.

INDEX

INDEX idx_name (`student_name`)  

Add an index to the student_name field.
Since a separate space for the index is required, the size of the table increases, but the processing speeds up when searching.

UNIQUE INDEX

UNIQUE INDEX idx_class_info(`grade`, `class`, `number`)  

Same as INDEX, except that constraints are added.
It prevents the addition of duplicate data for class, class, and number.

ENGINE, AUTO_INCREMENT, DEFAULT CHARSET


The last part to be explained is the ENGINE, AUTO_INCREMENT, and DEFAULT CHARSET parts at the end of the CREATE TABLE statement.

ENGINE

ENGINE=InnoDB  

Create a table in InnoDB format.

AUTO_INCREMENT

AUTO_INCREMENT=100  

The auto increment value, idx, starts from 100.

DEFAULT CHARSET

DEFAULT CHARSET=utf8  

The data stored in the table uses the UTF-8 format.

Next post