next
Table Column Types
What are the column types?
- integer: bool, tinyint, smallint, mediumint, int, bigint
- floating-point: float, double, fixed(digits,fraction)
- character: char(n<255), varchar(n<255), blob(s), text(s), enum, set
- time: date, time, year, timestamp
Column type modifiers
- all: not_null, primary_key, default (value)
- integer: unsigned, zerofill, auto_increment
- character: national, binary, ascii, unicode
- blob or text: tiny, medium, long (ie longblob)
Column operations (from within queries)
- numeric: = != + - / * < > max() tan() etc...
- character: like, rlike, substr, concat, length, replace, etc...
- time: adddate, addtime, current_date, datediff, str_to_date, etc...
Example table creation
| Id | Bird Name | Birthday | Male | Parents |
| 1 | Peter | 2004-11-05 | T | Mary, Bob |
| 2 | John | 2004-12-21 | T | Mary |
| 3 | Mary | 2003-05-02 | F | Ellen |
| 4 | Jane | 2005-07-12 | F | Mary, John |
| 5 | Bob | 2003-06-10 | T | Ellen |
| 6 | Ellen | 2002-11-27 | F | NULL |
create table birds ( id smallint unsigned auto_increment not null primary key, bird_name char(63) binary not null, birthday date not null, male bool not null, parents varchar(255), index (bird_name), index (male) );
Note: this table is not yet optimized
Watch out for
- NULL - adds overhead but can be useful
- varchar - adds overhead, gives a variable-sized table which may be slower
- binary - use as a character modifier to allow case-sensitive comparisons
- indexes - when comparing indexed values across tables, values are best at same size