MySQL Date TypesMySQL LAST_INSERT_IDMySQL InsertMySQL SelectMySQL WhereMySQL Group ByMySQL Order ByMySQL LimitMySQL LikeMySQL ISNULLMySQL BetweenMySQL JoinMySQL Left JoinMySQL Right JoinMySQL Cross JoinMySQL Self JoinMySQL Sub QueryMySQL UpdateMySQL DeleteMySQL ExplainMySQL IfMySQL Full-Text SearchMySQL INFORMATION_SCHEMAMySQL ConnectionMySQL Server Helpful FunctionsMySQL ViewMySQL Stored ProcedureMySQL FunctionsMySQL TransactionMySQL TriggerMySQL EventMySQL DeadlockMySQL DumpMySQL Reset Root PasswordMySQL interview – Advanced QueriesMySQL JsonMySQL BinlogMySQL Closure Table
MySQL Date Types
INTEGER
INT can be sign or unsign.
The ZEROFILL attribute can be used to add zero padding.
CREATE TABLE user(
id INT AUTO_INCREMENT PRIMARY KEY,
salary INT(7) ZEROFILL,
days_off INT(3) ZEROFILL
);
INSERT INTO mysql_playground.user (salary,days_off) VALUES (150,4);
AUTO_INCREMENT is used with INT but only with positive integers. MySQL is not supporting negative int with auto_increment at the time of writing.
DECIMAL
Decimal is used for numbers with decimal.
column_name DECIMAL(precision, decimals); column_name DECIMAL(precision,0) is the same as column_name DECIMAL(precision); column_name DECIMAL; // default precision is 10
Example: for DECIMAL(20,5), 5 for decimal part and 15 for the integer part.
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
total_cost DECIMAL(19 , 4 ) ZEROFILL
);
INSERT INTO `orders` (`total_cost`) VALUES ('120.43');
The above example shows the precision as well as the decimal. Decimal is very useful in accounting numbers.
BOOLEAN
Currently, boolean is not supported by mysql. MySQL is using TINYINT as in 0 for false and 1 for true.
CREATE TABLE members (
id INT PRIMARY KEY AUTO_INCREMENT,
deleted BOOLEAN
);
INSERT INTO `members` (`deleted`) VALUES (true); INSERT INTO `members` (`deleted`) VALUES (false);
Since boolean is stored as TINYINT, numbers like 2,3, or 4 can be stored like 1 and 0. true is 1 and false is 0. if you store any other int like 2, for example, it won’t pick up in the query.
CHAR
CHAR is a character type that stores 0-255 characters only. MySQL adds padding with spaces to values to the length you declare. MySQL will only store values up to the length declared and ignore the rest.
CREATE TABLE covers (
id INT PRIMARY KEY AUTO_INCREMENT,
status CHAR(3)
);
INSERT INTO `covers` (`status`) VALUES ('NO');
INSERT INTO `covers` (`status`) VALUES ('YES');
INSERT INTO `covers` (`status`) VALUES (' Y ');
INSERT INTO `covers` (`status`) VALUES ('TEST');
From the example above, you can see that ‘ Y ‘ is trimmed the trailing space. You can also see that TEST is cut off to TES because of the length declared.
MySQL trims trailing spaces when they retrieve char values. MySQL does not consider trailing spaces when it does a comparison(<,>,=) between chars.
VARCHAR
VARCHAR is a character type that stores up to 65,535 characters. if a column is less than 255 bytes the length of the prefix is 1 byte else if a column is greater than 255 bytes the length of the prefix is 2 bytes.
The maximum row size is 65,535 bytes.
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(3)
);
INSERT INTO `mysql_playground`.`products` (`title`) VALUES ('WASHER');
Depending on your version of mysql, if you insert/update a value of varchar to be greater than the length declared, it might cut off the value, which happens in this case, or throw an error.
VARCHAR, unlike CHAR, does not add padding with spaces. It also does not remove trailing spaces when it retrieves or compares values.
TEXT
TEXT is a character type that stores long text string from 1 byte to 4 GB. TEXT is not stored in server memory but in disk which is much slower.
TEXT has four types:
TINYTEXT – 1 Byte (255 characters),
TEXT – 64KB (65,535 characters),
MEDIUMTEXT – 16MB (16,777,215 characters),
LONGTEXT – 4GB (4,294,967,295 characters)
DATE
DATE default format is yyyy-mm-dd but of course you can change the default format using DATE_FORMAT(date, format)
To define a date column: created_at DATE.
MySQL DATE values range from 1001-01-01 to 9999-12-31. If you want other values from this range then you can use the int data type.
TIME
TIME default format is HH:MM:SS
To define a time column: created_at TIME.
DATETIME
DATETIME default format is YYYY-MM-DD HH:MM:SS. Datetime does not keep timezone.
TIMESTAMP
TIMESTAMP default is YYYY-MM-DD HH:MM:SS. TIMESTAMP is saved in the UTC timezone. When you store a TIMESTAMP, the mysql server will take that TIMESTAMP and convert it from the connection timezone to UTC timezone and then save in db. When retrieving, MySQL converts TIMESTAMP from UTC to the connection timezone.
MySQL Date TypesMySQL LAST_INSERT_IDMySQL InsertMySQL SelectMySQL WhereMySQL Group ByMySQL Order ByMySQL LimitMySQL LikeMySQL ISNULLMySQL BetweenMySQL JoinMySQL Left JoinMySQL Right JoinMySQL Cross JoinMySQL Self JoinMySQL Sub QueryMySQL UpdateMySQL DeleteMySQL ExplainMySQL IfMySQL Full-Text SearchMySQL INFORMATION_SCHEMAMySQL ConnectionMySQL Server Helpful FunctionsMySQL ViewMySQL Stored ProcedureMySQL FunctionsMySQL TransactionMySQL TriggerMySQL EventMySQL DeadlockMySQL DumpMySQL Reset Root PasswordMySQL interview – Advanced QueriesMySQL JsonMySQL BinlogMySQL Closure Table