MySQL Datatypes

Like other database systems we have looked at, MySQL stores data in one of several datatype families. In the case of MySQL, these families are numeric, character, and date/time. In addition to the standard datatypes, MySQL adds two character types—ENUM and SET—which we'll cover a little later.

Numeric Data

MySQL has some level of support for all the numeric datatypes specified in the SQL-92 standard. Furthermore, the MySQL team has given you a few more choices of datatypes to increase performance and reduce table size by using more precisely sized columns. For example, if you have a column whose value will always be in the range 0-255, you are correct in choosing the datatype UNSIGNED TINYINT as an alternative to the SQL-92 datatype SMALLINT. Making this one change cuts the storage requirement of that column in half, from 2 bytes to 1 byte. Although this is not a lot of savings for a small table, a large table, obviously, benefits a great deal.

For purposes of your web application, numeric data is any bit of data with which you need to perform arithmetic. The numeric datatypes supported by MySQL are TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, FLOAT, DOUBLE, REAL, DECIMAL, and NUMERIC. Also, you can use the MySQL-specific column definition attribute UNSIGNED to indicate a numeric column that stores only positive values. For example, a column to record visits to your website is a good candidate for an UNSIGNED column since you will always have 0 or more visits recorded.

Warning 

Warning Although UNSIGNED can be valuable in designing the database to support your web application, exercise caution when using it. Particularly, keep in mind that this attribute is specific to MySQL and might not be available to you if you decide later to port your application to another database. Also, when performing mathematic operations in which one value comes from a column defined with the UNSIGNED attribute, the result of the operation is itself UNSIGNED, which produces incorrect results when performing operations that you expect to yield a negative result.

TINYINT

TINYINT is an integer value ranging from –128 to 127; combined with the UNSIGNED attribute, this range (now excluding negative values) becomes 0 to 255. In the context of your web application, this column type is ideal for categorizing items into a relatively small number of groups. For example, a discussion website with a predetermined number of forums (and presumably fewer than 256) lends itself to having a forum identifier column defined as TINYINT UNSIGNED.

CREATE TABLE forums (  forum_id                   TINYINT UNSIGNED, forum_description          VARCHAR (255))

You can also use BIT and BOOL interchangeably with the TINYINT datatype.

SMALLINT

SMALLINT is an integer value ranging from –32768 to 32767; combined with the UNSIGNED attribute, the range of values is 0 to 65535. Again taking a discussion website as an example, a table for current discussion topics might be defined as follows:

CREATE TABLE topics (     forum_id            TINYINT UNSIGNED,    topic_id            SMALLINT UNSIGNED,     topic_description   VARCHAR (255) )

MYSQL DATATYPES 143MEDIUMINT

MEDIUMINT is an integer value ranging from –8388608 to 8388607; combined with the UNSIGNED attribute, the range of values is 0 to 16777215.

INT

INT or INTEGER is an integer value ranging from –2147483648 to 2147483647; combined with the UNSIGNED attribute, the range of values is 0 to 4294967295.

BIGINT

BIGINT is an integer value ranging from (get ready) –9223372036854775808 to 9223372036854775807; combined with the UNSIGNED attribute, the range of values is 0 to 18446744073709551615.

FLOAT

FLOAT is a single-precision, floating-point (real) number for which valid values can be 0 or are in the range of –3.402823466E+38 to –1.175494351E–38 and 1.175494351E–38 to 3.402823466E+38.

start sidebar
Precision—Single and Double

Computer science classes teach us that precision, in relation to the representation of numbers, corresponds to the number of decimal places that a number can have. If we store 3.1415 in a variable named nPI, we're telling the computer to store a value with 4 digits of precision (also called significant digits for you math whizzes out there) in the variable nPI. But the computer, of course, uses slots in its memory to hold variables and numbers (as well as everything else.) The larger the memory slot, the larger the number the computer can hold for any given variable. So, a single-precision variable has a given amount of memory it can use to hold a value. A double-precision has twice the amount of a single-precision.

Now, we're not intentionally being vague on the actual space allotted to single- and double-precision variables. The fact is, it varies with the computer. Typically, the definition of single precision is something like "the amount of memory corresponding to one computer word to store a number." So now we have a new term—a computer word. A computer word is the number of bits that you can store in one computer register. A computer register is… OK—enough of the computer science lesson. Suffice it to say that single-precision numbers are huge, and double-precision numbers are even larger.

end sidebar

DOUBLE

DOUBLE is a double-precision, floating-point number where valid values can be 0 or are in the range of –1.7976931348623157E+308 to –2.2250738585072014E–308 and 2.2250738585072014E–308 to 1.7976931348623157E+308.You can also use REAL interchangeably with the DOUBLE datatype.

DECIMAL

DECIMAL is also a double-precision, floating-point number. The effective range of a DECIMAL datatype is the same as that of a DOUBLE datatype; however, internally MySQL stores data of type

DECIMAL as a string, which allows the database designer more discreet control over the database storage requirement in terms of bytes used by a DECIMAL. You can also use NUMERIC interchangeably with the DECIMAL datatype.

Note 

Unlike the integer datatypes described earlier, the floating-point datatypes do not have the range of values that they can support extended by including the MySQL-specific UNSIGNED attribute to the column definition. Instead, the effect that the UNSIGNED attribute has on a floating-point column is to simply disallow negative values.

Character Data

Character data is essentially any string of data that does not need to be numeric for the purpose of performing arithmetic operations with it. The character datatypes supported by MySQL are CHAR, VARCHAR, TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT, TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB, ENUM, and SET.

CHAR

CHAR is a datatype for containing a fixed-length string of data. The length of the column can be defined as 1 to 255. Technically, you can also define a CHAR column to have length of 0, but unless you understand the reasons for doing so and have a specific purpose, avoid defining a column that way.

VARCHAR

VARCHAR is a datatype for containing a variable-length string of data. The length of the column can be defined as 1 to 255.

Note 

The primary difference between the CHAR and VARCHAR datatypes is in how MySQL stores data in the table and retrieves data from the table. A CHAR datatype pads values shorter than the defined length of the column with spaces when storing the value and trims spaces from the value when retrieving it. Therefore, a column defined with a length of 6 will take up 6 characters of storage space in the table regardless of the size of the value stored in it. A VARCHAR column also truncates values that are longer than the defined length of the column, but unlike CHAR, a VARCHAR allocates only as much space as is necessary to store the value—for values shorter than the defined length—plus 1 byte to record the actual length of the value.

TEXT, BLOB

TEXT, TINYTEXT, MEDIUMTEXT, and BIGTEXT are essentially all the same datatype and correspond with the related datatypes BLOB, TINYBLOB, MEDIUMBLOB, and BIGBLOB. These datatypes are all used to store large strings or binary objects, such as graphics. The difference between the TEXT types and the BLOB types is in how MySQL sorts the values. TEXT types are sorted without regard to case (case-insensitive), and BLOB types are sorted with regard to case (case-sensitive). Among the various versions of each of the TEXT and BLOB datatypes (TINY, MEDIUM, and BIG), the difference is the size of data that can be stored. Table 7.1 shows the lengths of these datatypes.

Table 7.1: MAXIMUM LENGTH OF DATATYPES

Datatype

Maximum Length (in bytes)

TINYTEXT, TINYBLOB

255

TEXT, BLOB

65535

MEDIUMTEXT, MEDIUMBLOB

16777215

BIGTEXT, BIGBLOB

4294967295

ENUM

The ENUM datatype lets you select a single string value from an enumeration of values defined at the time of table creation. If you attempt to set an ENUM field to a value not in the enumeration, MySQL inserts the value " " (empty string) instead. The ENUM datatype can support as many as 65535 individual values in the enumeration.

CREATE TABLE forums (  forum_id             TINYINT UNSIGNED,    forum_description  VARCHAR (255),     forum_status       ENUM("active", "inactive") )

SET

The SET datatype is similar to the ENUM datatype in that it lets you specify a list of allowable values at the time of table creation. The difference between these two datatypes is that a SET column can be assigned multiple values from the list of valid choices. The member list of a SET column definition can contain, at most, 64 values.

CREATE TABLE topics (   forum_id            TINYINT UNSIGNED,   topic_id            SMALLINT UNSIGNED,   topic_description     VARCHAR (255),   topic_flags         SET("moderated", "public", "indexed") ) 

Rows in this table might look like the following:

forum_id

topic_id

topic_description

TOPIC_FLAGS

1

1

"General Discussion"

"public",

"indexed"

1

2

"FAQ"

"public",

"moderated",

"indexed"

1

3

"Open Debate"

"public"

Notice that the value of the topic_flags column contains a combination of the possible values for the SET type column. The following is a typical SQL statement to return all the topics when "indexed" topic_flag is selected:

SELECT * FROM topics WHERE FIND_IN_SET('indexed', topic_flags) > 0;

Date/Time Data

The available date datatypes that MySQL provides are DATETIME, DATE, TIME, TIMESTAMP, and YEAR. Each datatype in the date/time family stores date- and time-related data with a different precision and has type-specific legal values.

DATETIME

The DATETIME datatype lets you store data that requires full date and time. A common use for this type is to record the exact date and time that a session within your web application began. For MySQL, the supported range of values for DATETIME is '1000-01-01 00:00:00' through '9999-12-31 23:59:59'.

DATE

The DATE datatype is useful if you do not need to record time-of-day. Good data fields for this column type include birthday, anniversary, and employment start date. The supported range of values for DATE is '1000-01-01' through '9999-12-31'.

TIME

The TIME datatype lets you store values that represent time. This datatype supports more than just a 24-hour range (HH:MM:SS); it actually provides the ability to store larger hour portions in the range of '–838:59:59' to '838:59:59'. This extended range of values makes a TIME column good for recording not only time-of-day data elements, but also elapsed time or other time counter values.

TIMESTAMP

The TIMESTAMP datatype lets you define a column that will record the time that INSERT or UPDATEoperations are performed on a particular row of data in the table. You can define multiple columns as TIMESTAMP, but the first of these is the only one that the MySQL server will automatically modify.

Note 

A TIMESTAMP column lets you track the time that data was changed. MySQL automatically updates the first TIMESTAMP column defined in a table when data in the row is updated or when the row is initially created. You can override this feature by explicitly setting the TIMESTAMP column to another value at the time of UPDATE or INSERT. You can automatically set any TIMESTAMP column to the current date/time by passing NULL to MySQL in an INSERT or UPDATE statement.

YEAR

The YEAR datatype lets you store only the year portion of a date and does so in only 1 byte of storage space. The range is limited to storing values from 1901 to 2155.

Warning 

Be careful when specifying dates in MySQL. In all cases that accept dates as values, MySQL performs only basic checks for validity. MySQL protects you from inserting date values with the month of 13, for example, but it does not verify that the February 29 is valid only in certain years and lets you specify a value of 09-31-2002 without complaint. MySQL simply checks that the month is between 01 and 12 and that the day is between 01 and 31.



Mastering Dreamweaver MX Databases
Mastering Dreamweaver MX Databases
ISBN: 078214148X
EAN: 2147483647
Year: 2002
Pages: 214

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net