Data Types
There's More to Discuss!
Storing Text
VARCHAR
CHAR
What's The Difference?
CHAR has a fixed length
Char(3) -> Only 3 Characters "Allowed"
The length of a CHAR column is fixed to the
length that you declare when you create the table.
The length can be any value from 0 to 255.
When CHAR values are stored, they are right-padded
with spaces to the specified length. When CHAR
values are retrieved, trailing spaces are removed
unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled.
CHAR is faster for fixed length text
zip codes: 59715, 94924
State Abbreviations: CA, NY
Yes/No Flags: Y/N
Otherwise...
USE VARCHAR
| Value | Char(4) | Storage | Varchar(4) | Storage |
|---|---|---|---|---|
| ' ' | ' ' | 4 bytes | ' ' | 1 byte |
| 'ab' | 'ab ' | 4 bytes | 'ab' | 3 bytes |
| 'abcd' | 'abcd' | 4 bytes | 'abcd' | 5 bytes |
Here's A Table
NUMBERS
INT
WHOLE NUMBERS
DECIMAL
DECIMAL(13, 2)
DECIMAL(5, 2)
Total Number Of Digits
Digits After Decimal
999.99
5 Digits
2 Digits
DECIMAL(5, 2)
But there's also...
FLOAT
and DOUBLE???
Store larger numbers using less space
BUT.....
(and it's a big BUT)
It comes at the cost of precision
| Data Type | Memory Needed | Precision Issues |
|---|---|---|
| FLOAT | 4 Bytes | ~7 digits |
| DOUBLE | 8 Bytes | ~15 digits |
Another Table
So Which Do I Use?
On To
Dates & Times
DATE
Values With a Date But No Time
'YYYY-MM-DD' Format
TIME
Values With a Time But No Date
'HH:MM:SS' Format
DATETIME
Values With a Date AND Time
'YYYY-MM-DD HH:MM:SS' Format
I Typically Use DATETIME
Let's Try It Out!
Some Useful Date Functions
Formatting
DATES
DATE
MATH
TIMESTAMPS?!
YOUR
TURN
What's a good use case for CHAR?
(just make one up)
CREATE TABLE inventory (
item_name ____________,
price ________________,
quantity _____________
);Fill In The Blanks
(price is always < 1,000,000)
What's the difference between
DATETIME and TIMESTAMP?
Print Out The Current Time
Print Out The Current Date (but not time)
Print Out The Current Day Of The Week
(The Number)
Print Out The Current Day Of The Week
(The Day Name)
Print out the current day and time using this format:
mm/dd/yyyy
Print out the current day and time using this format:
January 2nd at 3:15
April 1st at 10:18
Create a tweets table that stores:
- The Tweet content
- A Username
- Time it was created
Pat Yourself On The Back
MySQL 2.0: Data Types
By Colt Steele
MySQL 2.0: Data Types
- 7,137
