Databases - MySQL Types (GF Royle 2006-8, N Spadaccini 2008) Databases - MySQL Types 1 / 23 This lecture This lecture covers the main types of MySQL. Although learning how to use the MySQL types is important, there are a lot of types and it would be difficult to concentrate on all of them in one lecture (and the lecture would be pretty dull). Therefore although they are all grouped together for easy reference, we will actually go over part of this material in a number of different lectures. (GF Royle 2006-8, N Spadaccini 2008) Databases - MySQL Types 2 / 23 Types Using any typed language means learning the available types. Because there is no general mechanism to build new types out of old ones (like building classes in Java or structures in C) in MySQL there are a lot of types in MySQL many of which have several variants. There are four major groups of datatypes Numeric Types String Types Date and Time Types Spatial Types (GF Royle 2006-8, N Spadaccini 2008) Databases - MySQL Types 3 / 23 Integer Types Integer Types MySQL supports a variety of integer types INT, INTEGER A normal 32-bit integer with values from −231 to 231 − 1 just like a Java int. MEDIUMINT and SMALLINT 24-bit and 16-bit integers respectively. TINYINT An 8-bit integer (like Java’s byte type). BIGINT A 64-bit integer (like Java’s long type). (GF Royle 2006-8, N Spadaccini 2008) Databases - MySQL Types 4 / 23 Integer Types Signed types Unlike Java (but like C) you can declare any of these types to be unsigned for the situation where business logic dictates that the number can never be negative. For example, the type TINYINT holds values in the range −128− 127 while TINYINT UNSIGNED holds values in the range 0− 255. Normally it makes sense to declare things like id-numbers to be unsigned. (GF Royle 2006-8, N Spadaccini 2008) Databases - MySQL Types 5 / 23 Integer Types Non-integer types MySQL provides three basic non-integer types FLOAT or FLOAT(M,D) Single-precision floating point numbers. If M and D are specified, then the precision can be reduced to a total of M decimal digits with D after the decimal point. DOUBLE or DOUBLE(M,D) Double-precision floating point numbers DECIMAL or DECIMAL(M) or DECIMAL(M,D) Fixed-point numbers with M decimal digits and D after the point For example, we might use DECIMAL(8,2) to store dollars-and-cents values. (GF Royle 2006-8, N Spadaccini 2008) Databases - MySQL Types 6 / 23 Integer Types The ZEROFILL attribute Any of the unsigned numerical types can also be varied by the addition of the attribute ZEROFILL. This will cause any displayed values of that type to be padded with zeros on the left — in this example f1 has type TINYINT ZEROFILL, f2 has type MEDIUMINT ZEROFILL and f3 has type INT ZEROFILL. +------+----------+------------+ | f1 | f2 | f3 | +------+----------+------------+ | 005 | 00000006 | 0000000007 | | 101 | 00000202 | 0000000303 | +------+----------+------------+ (GF Royle 2006-8, N Spadaccini 2008) Databases - MySQL Types 7 / 23 String Types String Types MySQL has 5 basic string types CHAR and VARCHAR BINARY and VARBINARY BLOB and TEXT ENUM SET (GF Royle 2006-8, N Spadaccini 2008) Databases - MySQL Types 8 / 23 String Types Character Strings CHAR(M) is a fixed-length string of M characters, while VARCHAR(M) is a variable-length string of at most M characters. A CHAR(M) field always uses the full M characters no matter what it contains, and so can be wasteful of space if there are many fields shorter than the longest. However the fixed size of a CHAR(M) column makes some internal operations much faster and so if the values all happen to be about the same size, then CHAR(M) is the best choice. Something like student login names would be a perfect candidate for a CHAR(8) type. Note: The full details of the differences between CHAR and VARCHAR are more complicated and technical than this brief description. (GF Royle 2006-8, N Spadaccini 2008) Databases - MySQL Types 9 / 23 String Types Character sets and collations The CHAR and VARCHAR types represent strings of characters — but of course, representing characters in a computer requires specifying a character set. For example, a string might be a string of ASCII characters (from the character set ascii or a string of Chinese characters (from the character set big5). Moreover even among languages that use the same character set it is possible that the order of the characters is considered to be different – a collation is a set of rules that specify how to order strings. MySQL allows the user to specify both a character-set and a collation individually for any string type fields. (GF Royle 2006-8, N Spadaccini 2008) Databases - MySQL Types 10 / 23 String Types The importance of collations By default MySQL uses the latin1 character set, but even that has more than one different collation. mysql> create table collationtest ( -> f1 CHAR(8) COLLATE latin1_swedish_ci, -> f2 CHAR(8) COLLATE latin1_bin); The collation latin1_swedish_ci is the default “English language” collation, while latin1_bin is the binary order according to the encoding of the character set. Why is this important? (GF Royle 2006-8, N Spadaccini 2008) Databases - MySQL Types 11 / 23 String Types An example mysql> SELECT * FROM collationtest; +-------+-------+ | f1 | f2 | +-------+-------+ | apple | apple | | Apple | Apple | +-------+-------+ 2 rows in set (0.00 sec) The table contains just two rows both the same. (GF Royle 2006-8, N Spadaccini 2008) Databases - MySQL Types 12 / 23 String Types Collation affects selection SELECT * FROM collationtest WHERE f1 = ’apple’; +-------+-------+ | f1 | f2 | +-------+-------+ | apple | apple | | Apple | Apple | +-------+-------+ 2 rows in set (0.00 sec) SELECT * FROM collationtest WHERE f2 = ’apple’; +-------+-------+ | f1 | f2 | +-------+-------+ | apple | apple | +-------+-------+ 1 row in set (0.01 sec) The default collation gives case-insensitive string comparisons, while the binary collation treats a and A as different. (GF Royle 2006-8, N Spadaccini 2008) Databases - MySQL Types 13 / 23 String Types Binary Types It is quite common to use characters and strings to encode data as well as for human-readable strings. In this case, collations make no sense and to ensure that queries are not mangled by default collations changing, such data types can be declared as BINARY or VARBINARY with the same syntax as CHAR and VARCHAR. The binary types are then just treated as a sequence of bytes with no particular interpretation as characters, and comparisons and sorting are based purely on their numerical value. (GF Royle 2006-8, N Spadaccini 2008) Databases - MySQL Types 14 / 23 String Types BLOB and TEXT For (more) truly variable-sized fields, you can use either BLOB or TEXT types. The difference between them is that TEXT is viewed as a string of characters hence has a character set and a collation, whereas a BLOB (Binary Large OBject) is a string of bytes. Therefore a TEXT field is used for large amounts of textual data (for example, books, blog-entries, web-pages etc) while a BLOB is used for large amounts of pure data (for example, compressed files, images etc). (GF Royle 2006-8, N Spadaccini 2008) Databases - MySQL Types 15 / 23 String Types Variants of BLOB and TEXT There are four variants of each type which determine the maximum amount of storage. TINYBLOB, TINYTEXT Maximum size of 28 − 1 bytes/characters. BLOB, TEXT Maximum size of 216 − 1 bytes/characters (64 K). MEDIUMBLOB, MEDIUMTEXT Maximum size of 224 − 1 bytes/characters (16 Mb). LONGBLOB, LONGTEXT Maximum size of 232 − 1 bytes/characters (4 Gb). (GF Royle 2006-8, N Spadaccini 2008) Databases - MySQL Types 16 / 23 String Types Inserting BLOBs One immediate question is how to get binary data into and out of a table. For example, suppose we have a table student that will store student information including their student photograph. CREATE TABLE student ( snum INT NOT NULL, sname VARCHAR(40), photo BLOB ); INSERT INTO student VALUES(1058475, "John Smith", LOAD_FILE(’/tmp/smith.jpg’)); (GF Royle 2006-8, N Spadaccini 2008) Databases - MySQL Types 17 / 23 String Types Extracting BLOBs SELECT photo FROM student; will give different results depending on which client is used. Some are clever enough to figure out that the data is binary and just display some sort of icon, but others will just dump the raw data on the screen. To pull out the photo into another file, use a variant of SELECT SELECT photo INTO DUMPFILE ’/tmp/smith2.jpg’ FROM student; The INTO DUMPFILE says to simply put the raw data into the file. The resulting file will be owned by the mysql server. (GF Royle 2006-8, N Spadaccini 2008) Databases - MySQL Types 18 / 23 String Types Enumerated Types The ENUM data type is used when there are a fixed number of possible values (currently MySQL limits an ENUM to 65535 distinct values). Enumerations are very useful for category values where an entity set is partitioned into different subsets. degree ENUM(’BA’, ’BSc’, ’BE’, ’BCompSc’) size ENUM(’S’,’M’,’L’,’XL’) lab ENUM(’2.03’,’2.05’,’2.07’,’2.09’) A field of ENUM type may be NULL or takes on exactly one of the specified values. (GF Royle 2006-8, N Spadaccini 2008) Databases - MySQL Types 19 / 23 String Types Sets The SET datatype is defined similarly to the ENUM type by listing a set of strings. However a variable of SET type can take a subset of those strings as its value, not just one. This datatype can be used when the values are not mutually exclusive. toppings SET(’cheese’, ’onions’, ’tomato’, ’mushrooms’, ’capsicum’) interests SET(’reading’, ’movies’, ’sport’, ’gardening’) (GF Royle 2006-8, N Spadaccini 2008) Databases - MySQL Types 20 / 23 String Types Using sets Sets are implemented in MySQL by using bit-patterns, and the syntax for accessing set-valued data is heavily dependent on the bitwise operators and these implementation details. Consider the SET of interests (maybe from an online dating site) ’reading’, ’movies’, ’sport’ ,’gardening’ We can represent a subset of these 4 items by a collection of 4 bits as follows: 1000 represents ’reading’ 0100 represents ’movies’ 0010 represents ’sport’ 0001 represents ’gardening’ (GF Royle 2006-8, N Spadaccini 2008) Databases - MySQL Types 21 / 23 String Types Multiple values Now a set of interests is represented simply by setting the appropriate bits to 1. 1100 represents ’reading, movies’ 0111 represents ’movies, sport, gardening’ Internally these are simply stored as numbers, but MySQL translates the numbers into readable sets of values for output purposes. The numbers that are used are the bit-patterns viewed as binary numbers in reverse — thus ’movies, sport, gardening’ has the value 14. (GF Royle 2006-8, N Spadaccini 2008) Databases - MySQL Types 22 / 23 String Types Querying sets A query involving a set-valued field can be done in a “pattern-matching” fashion or using the underlying binary representation. For example, to answer the query Who is interested in gardening? we could use either SELECT * FROM people WHERE interests LIKE ’%gardening%’; or SELECT * FROM people WHERE interests & 8 <> 0; (GF Royle 2006-8, N Spadaccini 2008) Databases - MySQL Types 23 / 23