Data Types
Every column has to be told, in advance, what kind of value it will hold. That is its data type. It is not paperwork: it is the promise that lets the database sort dates properly, add up marks correctly, and refuse the word “hello” where a roll number belongs.
1The seven you need
| Type | Holds | Example value | Use it for |
|---|---|---|---|
| int | A whole number | 2035 | Roll numbers, class, quantity |
| bigint | A very large whole number | 411122223333 | Aadhaar, phone numbers, big IDs |
| float | A number with a decimal part, stored approximately | 83.75 | Marks, percentages, measurements |
| decimal(p,d) | A number with decimals, stored exactly | 1234.56 | Money — fees, salary, prices |
| char(n) | Text of a fixed length, exactly n wide | 'IND' | Codes: state, grade, Y/N flags |
| varchar(n) | Text of a varying length, up to n | 'Gangtok' | Names, cities, addresses |
| date | A calendar date | '2008-08-13' | Birth dates, joining dates |
Declaring them looks like this — the type comes straight after the column name. Here is one table using all seven:
And one row of real values going in and coming back out:
2int and bigint
Both hold whole numbers. The difference is simply how big a number fits. An int stops a little above two billion, which is plenty for a roll number and nowhere near enough for an Aadhaar number:
Twelve digits will not fit. Widen the column to bigint and the same value is stored without complaint — which is exactly why the teachers table declares aadhar bigint.
varchar(10) for them instead. Use bigint when the value is genuinely a quantity or an id you will compare numerically.3float and decimal — and why money needs decimal
Both hold numbers with a decimal part. float stores an approximation in binary, and decimal stores the digits exactly. For marks nobody notices. For money everybody notices. Watch the same two values added up in both:
0.1 + 0.2 should be 0.3. The float column says 0.30000000447034836; the decimal column says 0.30. Neither is a MySQL bug — 0.1 has no exact binary form, so a float can only get close, and the error shows up once you add several of them together. A fee register that is a few paise out every time is not acceptable, and that is the whole argument for decimal.
decimal(8,2) holds six digits before the point and two after — up to 999999.99. It is not “8 before and 2 after”.4What the number in brackets means
For text, varchar(30) does not mean thirty of anything. It is the maximum number of characters the column will accept. A name of six letters fits comfortably; one of thirty-one does not fit at all:
Column e was declared char(3) and "ABCD" is four characters, so the whole row is refused. Nothing is quietly cut short.
5char vs varchar
Both hold text and both take a length. The difference is what happens to the space you did not use.
Always reserves room for 10 characters, whatever you store. Best when every value really is the same length — a state code, a grade, a Y/N flag.
Reserves only what the value needs, up to 10. Best when lengths differ — names, cities, addresses. This is the one you will use most.
That difference is usually described as invisible, but there is one place you can see it. Store 'hi ' — “hi” followed by three spaces — in both, then wrap each in brackets so the spaces show:
char(10) holding 'hi' gives you back “hi” plus eight spaces. It does not: as the output above shows, char strips trailing spaces on the way out — length 2 — while varchar keeps every one you gave it — length 5. So the fixed-vs-variable difference is real, but it is about how the value is stored, and the one visible effect is that char quietly loses trailing spaces.| char(n) | varchar(n) | |
|---|---|---|
| Width | Fixed — always n | Variable — as much as the value needs |
| Storage used | The same for every row | Grows and shrinks with the value |
| Trailing spaces | Removed when the value is read back | Kept exactly as supplied |
| Best for | Codes of a known, equal length | Names, cities, anything that varies |
| Speed | Slightly faster, being a fixed size | Slightly slower, but saves space |
| Example | char(3) for 'IND' | varchar(30) for 'Rajesh Kumar' |
6Dates have exactly one format
'YYYY-MM-DD'
year first, four digits, hyphens — and quotes around the whole thing
The day-first order you write by hand is not accepted. This is a favourite exam trap and a genuine everyday mistake:
05-06-2010 the 5th of June or the 6th of May? It depends which country you are in. 2010-06-05 can only mean one thing.7Putting the wrong thing in
This is the data type earning its keep. Because grade is an int, nobody can ever type “twelve” into it, and so every query that compares grades can rely on finding numbers there.
8Which values need quotes
char, varchar and date values.
int, bigint, float and decimal values.
9Recap
Whole numbers. bigint when int overflows — Aadhaar, phone.
Approximate / exact. Money always takes decimal.
Fixed / variable text. char drops trailing spaces.
'YYYY-MM-DD', quoted. No other order is accepted.
Which type should hold a 12-digit Aadhaar number?
Why is decimal preferred over float for money?
decimal(8,2) can hold the largest value…
'hi ' (with three trailing spaces) is stored in both char(6) and varchar(6). What do length() return?
How must 13 August 2008 be written?