Selecting MYSQL Field Data Types

Every field of a MySQL table incorporates a data type as one of its primary attributes. This data type plays an important role in enforcing the integrity of the data in a MySQL database and in making this data easier to use and manipulate.

Intelligent use of data typing can result in smaller databases and tables, efficient indexing, and quicker query execution; indifferent, ham-handed use of types can result in bloated tables, wasted storage space, inefficient indexing, and a gradual deterioration in performance. For example, using a VARCHAR type on a field that is meant for numeric or date values could result in unexpected behavior when you perform calculations on it, just as using a large TEXT field for small string values could lead to a waste of space and inefficient indexing. Wise database architects, therefore, make it a point to be fully aware of the various data types available in a system, together with the limitations and benefits of each, prior to implementing a database-driven application; the alternative can be costly in terms of both time and money.

Numeric Types
For integer values, MySQL offers you a choice of the TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT types, which differ from each other only in the size of values they can store. Use the TINYINT and SMALLINT types for small integer values, the INT type for larger integer values, and the BIGINT type for extremely large values. For floatingpoint values, use the FLOAT and DOUBLE types for single-precision and double-precision floating point values, respectively. And, finally, for decimal values, use the DECIMAL data type.

When defining an integer field, you can include a width specifier in parentheses. This width specifier controls the padding MySQL applies to the field when retrieving it from the database. For a field defined as BIGINT (20), MySQL will automatically pad the value to 20 characters before displaying it.

When defining floating-point and decimal fields, MySQL enables you to include both a width specifier and a precision specifier. For example, the declaration FLOAT (7,4) specifies that displayed values will not contain more than seven digits, with four digits after the decimal point. You can also add the ZEROFILL attribute to pad values with leading zeroes, and the UNSIGNED attribute to force a field to only accept positive values.

Character and String Types
MySQL lets you store strings up to 255 characters in length as either a CHAR or VARCHAR type. The difference between these two types is simple: CHAR fields are fixed to the length specified at the time of definition, while VARCHAR fields can grow and shrink dynamically, based on the data entered into them. This makes VARCHAR fields more suitable for fields that accept variable-length data, and CHAR fields better for fields that always contain values of the same length. Both CHAR and VARCHAR type definitions must include a width specifier in parentheses, as with numeric type definitions. Thus, the definition CHAR (10) creates a field whose length remains exactly 10 characters, regardless of what is entered into it, while the definition VARCHAR (10) creates a field whose length can range anywhere between 0 and 10 characters, depending on what is entered into it.

Text and Binary Types
MySQL enables you to store strings greater than 255 characters in length as either a TEXT or BLOB type. The difference between TEXT and BLOB types is minimal at best:
TEXT types are compared in a case-insensitive manner, while BLOB types are compared in a case-sensitive manner. For this reason, BLOBs are usually used to store binary data, while TEXT fields are used to store ASCII data. Depending on the size of the string you’re trying to store, MySQL offers you a choice of the TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT types (for ASCII text blocks) and the TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB types (for binary data).

Date and Time Types
For simple date and time values, MySQL offers the intelligently named DATE and TIME data types. The DATE type is used to store date values consisting of year, month, and day components, while the TIME type is used for time values or durations consisting of hour, minute, and second components. Both DATE and TIME types can be used for values in either numeric (YYYYMMDD and HHMMSS) or string (‘YYYY-MM-DD’ and ‘HH:MM:SS’) format.

If what you need is a combination of the two, consider using the DATETIME or TIMESTAMP types, both of which let you specify both date and time values in a single field. The difference between the two lies in how the values are stored: DATETIME fields are stored in the form ‘YYYY-MM-DD HH:MM:SS’, and TIMESTAMP fields are stored in the form YYYYMMDDHHMMSS.

Finally, for simple applications that only need to store the year, MySQL offers the special YEAR type, which accepts a four-digit year value. It’s worthwhile to use this value if your application deals mostly with the year component of a date value, because a field marked as YEAR occupies 1 byte on disk (as compared to a DATETIME or DATE field, which can occupy up to 8 bytes). MySQL YEAR fields can accept any value in the range 1901 to 2155.

When inserting records into a table containing a TIMESTAMP field, MySQL automatically fills that field with the current date and time if no other value was specified. To accomplish the same thing with other date/time fields, use the NOW() function.

For situations where a field value must be selected from a predefined list of values, MySQL offers the ENUM and SET data types. For both these types, a list of predefined values must be included as part of the type definition. An ENUM field definition can contain up to 65,536 elements, while a SET field definition can hold up to 64 elements. For a field marked as an ENUM field, only one of the predefined values may be selected, whereas for a field marked as a SET field, zero, one, or more than one of the pre-defined values may be selected. ENUM fields are best suited for mutually exclusive values, while SET fields are best suited for independent values. As an example, the definition ENUM (‘red’, ‘green’, ‘yellow’) forces entry of any one of the three values, while the definition SET (‘mon’, ‘tue’, ‘wed’, ‘thu’, ‘fri’) allows entry of none, one, or all of the five values. In addition, SET values are stored as bits, making it possible to perform bitwise comparison and sorting operations on them.

Data Type Selection Checklist
To decide the data type for a field, take into account the following factors:
• The range and type of values that the field will hold
• The types of calculations you expect to perform on those values
• The manner in which the data is to be formatted for display purposes
• The manner in which the data is to be sorted and compared against other fields
• The available subtypes for each field and their storage efficiencies

By taking all of these factors into consideration when designing your database, you reduce the chance of incompatibilities and storage inefficiencies.

Source of Information : MCGraw Hill - SQL the Complete Reference 3rd Edition


Subscribe to Developer Techno ?
Enter your email address:

Delivered by FeedBurner