Basic column/field data types in MySQL Categories: I. numeric II. string III. temporal I. Numeric: A.integer B.floating-point C.fixed-point ************************************************************************ A. integer: INT or INTEGER 4-byte int. range -2G--+2G TINYINT 1-byte -128-+127 SMALLINT 2-byte -32K-+32K MEDIUMINT 3-byte -8M-+8M BIGINT 8-byte -8E-+8E Optionally declared with display width: num INT(5) column 5 wide unless overridden by more-digits value. Default display width is what's needed for max length value, e.g INT is INT(11) e.g. -1234567890 UNSIGNED modifier shifts range to positive, e.g. INT UNSIGNED is 0-+4G no negative values, clipped to 0 with warning, or Error in Strict mode. B. floating-point: approximate real numbers in CPU's native format: efficient FLOAT 4-byte DOUBLE 8-byte Optionally declared with 'precision' (width) and scale (#digits after .): num DOUBLE(6,1) C. fixed-point/exact real numbers DECIMAL(display_width,scale) scale is # of digits to right of decimal point price DECIMAL(6,2) scale of 2 good for monetary values total DECIMAL #10,0 default width and scale total DECIMAL(8) #0 default scale total NUMERIC synonym of Decimal stored as string of digits (no loss of precision) but converted to ? floating-point for calculation (possible loss of precision, rounding error). 123.456 real literals are exact (i.e. Decimal) 1.23456E2 f.p. Note: if no need to perform arithmetic, make it string type, even if all digits. (string is faster to process ??) eg. zip, SSN, phone# BIT(n) bitfield of n bits. n:1 to 64 b'1011' ************************************************************************ II. String non-binary strings have a character set (default: Latin1) and collation (default: Swedish CI). Show charset; Show collation; select @@collation_connection, @@character_set_connection; CI case-insensitive: A a same. CS: A before a but both before Bb. only Binary string is ASCII CHAR(size) fixed-length string. Max of 255. always stored as size chars. actual string can be shorter. if shorter, padded with spaces that are truncated at retrieval. VARCHAR(size) variable-length string. Max of 64K. saves memory. 4:a too-long value is truncated to fit the size with warning. 5: error in Strict modes BINARY modifier makes it case-sensitive: f1 CHAR(5) BINARY varchar trailing spaces NOT removed. char textual data up to 4GB extra B storage TINYTEXT 256 1 TEXT 64K 2 MEDIUMTEXT 16M 3 LONGTEXT 4G 4 essentially a big Varchar raw binary data (byte stream) up to 4GB: images, clips, compressed or encrypted data. BINARY(size) analog of Char VARBINARY(size) analog of Varchar binary large object plus extra bytes of storage TINYBLOB 256 1 BLOB 64K 2 MEDIUMBLOB 16M 3 LONGBLOB 4G 4 essentially a big Varchar Binary. insert programmatically or pre-processing before interactively. multimedia data in OS files or in DB? issues: backup synchronization of DB vs. filesystem, replication to slave servers, many OS files vs. DB file(s), ... MyISAM tables without any variable-sized columns (i.e. no Varchar, Blob, Text) are stored with fixed-length rows. Various silent conversions between Char and Varchar occur to improve memory and processing.? still? NO char silent conversion to varchar. Enumerated types: (a list of the only possible string values) restricts the possible values that the stringy column can have. tighter control=higher integrity of the data. ENUM('val1','val2',...) can be any one of the up to 64K listed "members". case-insensitive string members unless Enum Binary. sex ENUM('m','f') instead of CHAR(1). No other value can be in this column. species ENUM('cat','dog','snake','bird','hamster') bonus ENUM('n','y') bonus ENUM('no','yes') rating ENUM('good','bad','ugly') boolean ENUM('true','false') #true and false are keywords continent ENUM('Africa','Antarctica','Asia','Australia','Europe','North America','South America') state ENUM('AK','AL','AR','AZ','CA','CO','CT','DC','DE','FL','GA','HI','IA','ID','IL','IN','KS', 'KY','LA','MA','MD','ME','MI','MN','MO','MS','MT','NC','ND','NE','NH','NJ','NM','NV', 'NY','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VA','VT','WA','WI','WV','WY' ) Strict mode: Inserting/Updating invalid value is error. (except can insert Null, if Null allowed) otherwise 0 Queries are same, no difference. Except Order By is in enum order, not necessarily alphabetical. Cast(my_enum_field AS Char) internal representation is the integer position of the member, starts at 1: Insert ... set continent='asia' #3 stored Insert ... set continent='qwerty' #0 stored for invalid member unless Strict mode: error ? Select continent ... #empty string '' displayed for invalid member Select continent+0 ... #to see integer position value SET('val1','val2',...) any subset (combo) of up to 64 string values case-insensitive string members unless Set Binary. features SET('adorable','housebroken','redzone','fixed','mellow') symptom SET('sneezing','runny nose','stuffy head','red eyes') 1 2 4 8 row stores bitmap of 1,2,3,4,8 bytes for up to 8,16,24,32,64 members: Insert ... set symptom='runny nose,red eyes' #10 stored in 1 byte Insert ... set symptom='' #no symptoms. empty set #0 stored Can be Inserted in any order: Insert ... set symptom='runny nose,red eyes,sneezing' but will display in created order Inserting/Updating invalid members is Error in Strict mode. (nonStrict: ignored with warning) Insert ... set symptom='runny nose,retching' Select symptom+0 ... #to see the stored integer Querying: No special operators etc. select * from pet where features like '%fixed%'; select * from pet where features='fixed'; and nothing else select * from pet where features=''; no features select * from pet where features not like '%redzone%'; select features ... Enum and Set values can be assigned as integers or searched as integers, but avoid. ************************************************************************ III. Temporal DATE from year 1000 to 9999. displayed in YYYY-MM-DD 1000-01-01 to 9999-12-31 3 bytes storage. (might be able to insert earlier but unexpected results may happen) expects date entry to be in this format (or close: leading 0s not needed, other delimiter OK). DATE_FORMAT() to change display format. YEAR 1 byte storage. Year(2) 1970-2069 i.e. 70-69 Year(4) 1901-2155 is default TIME time of day or elapsed time: range: -838 hours to 838 hours. HH:MM:SS -838:59:59 to 838:59:59 3 bytes storage. TIME_FORMAT() to change display format. DATETIME combo of date and time except time is time of day only. YYYY-MM-DD HH:MM:SS Every second from year 1000 to 9999 8 bytes storage. TIMESTAMP stored as seconds since 1970-01-01 (Unix epoch). 4 bytes storage. in UTC automatically Not Null attempt to assign Null sets to current time. never is Null (unless explicity declared as Null). but can be zero if given default. Any change to row sets the first timestamp column to current time (useful as modification time of the row). Or, if Default Current_Timestamp used on any one Timestamp column it is initialized to row creation time (and doesn't change on update). Or, if On Update Current_Timestamp used on any one Timestamp column it is initialized to zero at creation but changed to current time on update. Or, both Default Current_Timestamp On Update Current_Timestamp initialized at creation and changed on update. To have both a creation column and change column: creation Timestamp Default 0, updated Timestamp On Update Current_Timestamp,... Insert Null into creation column. Time zone: Set time_zone='+02:00'; Select @@time_zone; temporalValue +|- INTERVAL n SECOND|MINUTE|HOUR|DAY|MONTH|YEAR birth + Interval 6 Month ************************************************************************ zero: numeric is 0, string is '' (empty string) temporal is 0000-00-00 00:00:00 ************************************************************************ Automatic type conversion. in non-strict mode. 4: Insert/Update to values outside of range get silently "clipped" to nearest endpoint. 5: Error if SQL "mode" is Strict or 'Traditional' (default is '' but conf file changes) Out of range, incorrect data type, missing value w/out default are Errors. Select @@sql_mode; Set sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; i.e Strict Set sql_mode='traditional'; ... Lenient '' Strict 'STRICT_TRANS_TABLES[,STRICT_ALL_TABLES]' 'Traditional' (very strict) numerics converted to string no problem: 123 -> '123' numeric-looking strings converted to numeric no problem: '123' -> 123 '123.45' -> 123.45 warning if string not (completely) numeric-looking or converted to 0 if prefix not numeric: '123asdf' -> 123 '2005-04-23' -> 2005 'asdf' -> 0 temporal-looking strings converted to temporals no problem: '2005-04-23' -> '2005-04-23' warning and conversion to temporal zero if string not temporal-looking: 'asdf' -> '0000-00-00' '06-12-1978' -> '0000-00-00' '123asdf' -> '0000-00-00' impossible bad dates (e.g. 32 Jan) converted to temporal zero: '2005-01-32' -> '0000-00-00' plausible bad dates (e.g. 30 Feb) accepted: '2005-02-30' -> '2005-02-30' ... MySQL parses but doesn't yet implement? CHECK(...) data integrity constraints. id int check(id<1000)... ************************************************************************ Null: unknown, or missing, or not applicable. Not a value; an indicator of the absence of a known value. Recommended to avoid Nulls. Is the default value for a column that can be Null. For a Not Null column, default value is, by default, zero. Error if try to Create Table with Default Null for a Not Null column. Error if try to (single-row) Insert a Null into a Not Null column. Warning if try to (multiple-row) Insert a Null into a Not Null column; zero is inserted instead. A Timestamp or Auto_increment column will never be Null; inserting Null results in current time and next increment value, respectively. n/0 is Null Any arithmetic/comparison with Null results in Null: Null+1 Null but use IS [NOT] NULL instead Null<=>Null is true Null<=>value is false For Order By, Group By, and Distinct Nulls are considered identical. Nulls sort together, group together, and are not distinct. Aggregate functions, except Count(*), ignore Nulls. Aggregate function of empty set or all Nulls is Null.