Module 2: Defining a Database
This module will explain the different types of data, their uses, and why it is important to define your data correctly. With an understanding of the different types of MySQL data types, you can plan your database and its tables, identifying the name , type, size , and special settings for each field. Next , you will learn the commands to create databases and tables in those databases. The projects in this module will lead you through creating a database with tables, and then loading data into your newly made database tables, which will give you the foundation for the projects in the later modules.
Critical Skill 2.1 Understand MySQL Data Types
You need to choose the correct data type when defining the fields of each record as you create the tables for your database. Some of the reasons that it is important to use the correct data type are straightforward. For instance, you can put numeric values in a text field, but you cannot use those values to do math. Therefore, any number you want to use for mathematical purposes (to total the number of items sold, for example) must be in a field that has been defined as some sort of numeric data type.
However, some of the reasons for choosing a particular data type for a field can be murkier. For example, you can make a text field that either is a fixed length or that automatically reflects the size of the data contained in it. The reasons why one would be better than the other are not immediately apparent, and indeed, usually depend on individual circumstances and requirements.
More than one company has called in expensive outside consulting support after several days of trying to troubleshoot a database problem, only to find that something as simple as the data type defining a field was all that was halting their database from proper functionality. So, taking the time to understand the various data types and selecting the most appropriate one can save you time, trouble, and sometimes money.
MySQL data types can be grouped into four sets: numbers , text, date and time, and defined group types. Table 2-1 lists the data types in each category.
| Numbers | Text | Date and Time | Defined Group |
|---|---|---|---|
| Integers: INT, TINYINT, SMALLINT, MEDIUMINT, BIGINT | Normal strings: CHAR | Date and time: DATETIME | Enumerated lists: ENUM |
| Fixed decimals: NUMERIC , DECIMAL | Variable strings: VARCHAR | Date and time: TIMESTAMP | Sets of similar data: SET |
| Floating point: FLOAT , DOUBLE | Long text blocks: TINYTEXT, MEDIUMTEXT, TEXT, LARGETEXT | Date only: DATE | |
| Binary Long Objects: BLOB, TINYBLOB, MEDIUMBLOB, LONGBLOB | Time only: TIME | ||
| Year only: YEAR |
Within each set of data types, the differences are not always clear from the word or partial word used in the code. For instance, on the surface, DATETIME and TIMESTAMP would seem to give you much the same information, and on a basic level, this is true. They both tell you time and date information; however, they do it in different formats and within different boundaries.
Before you start creating MySQL tables, you should understand the differences between data types and the reasons you might choose one data type over another. You don t need to memorize the details about each type. You can always check the tables provided here for information about code syntax and number boundaries.
Number Data Types
The number data types cover a lot of ranges and byte sizes. Don t be intimidated by the wide variety of them. Make sure you understand the underlying concepts, and mark these pages for easy reference later.
Integers: TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT
An integer, in case it has been awhile since you were last forced to take a math class, refers to any of the natural numbers, the negatives of those numbers, or zero. There are five types of integer data in MySQL: TINYINT , SMALLINT , MEDIUMINT , INT , and BIGINT . Any of these types can be either signed or unsigned.
-
A signed integer can hold both negative and positive numbers and zero.
-
An unsigned integer can hold only positive numbers and zero.
The default value is signed. If you want the field to hold only positive numbers and zero, you must place UNSIGNED after the closing parenthesis:
<field_name> INT(width_value) UNSIGNED
The main difference between the integer data types is how much space each takes up. As a result of that specific size, the integer must hold numbers within a specific range. For instance, a TINYINT takes up only 1 byte of space. Because of this small space, it can hold only 256 sequential numbers: from 0 to 255, if unsigned, or from “128 to 127, if signed. In both these cases, the TINYINT holds 256 consecutive numbers, but the act of designating it as signed or unsigned radically changes which numbers it can hold.
If you define a field as TINYINT and the field s value exceeds the maximum size, the value will be edited to reflect the highest number possible. This means that any number larger than the upper range for that field will be automatically truncated. For instance, in a field defined as an unsigned TINYINT , any number 256 or larger will be entered in the field as 255. In the case of an automatically incremented field, such as one that contains customer numbers, it will flawlessly count from 1 to 255, and then repeat 255 for each entry thereafter. You might spend a lot of time and effort looking for the cause of the refusal to increment (since it s human nature to look for big causes before looking for little ones).
In the case of signed fields, the lower range works in the same way. No number smaller than the bottom of the range will be placed in the field.
Table 2-2 lists each integer type, along with the amount of bytes it takes up and the exact range of its signed and unsigned versions.
| Integer Type | Bytes | Signed | Unsigned |
|---|---|---|---|
TINYINT | 1 | “128 through 127 | 0 through 255 |
SMALLINT 2 | 2 | “32,768 through 32,767 | 0 through 65,535 |
MEDIUMINT 3 | 3 | “8,338,608 through 8,388,607 | 0 through 16,777,215 |
INT 4 | 4 | “2,147,483,648 through 2,147,483,487 | 0 through 4,294,967,295 |
BIGINT 8 | 8 | “9,223,372,036,854,775,808 through 9,223,372,036,854,775,807 | 0 through 18,446,744,073,709,551,615 |
For a beginner, it can be difficult to guess just how big a particular range of numbers may need to grow. A good first rule of thumb is, when in doubt, make it bigger. However, if you were to consider only that, you would always choose the BIGINT data type, so there are other considerations.
Most people will probably never need an integer space that will hold a number that has more than 19 digits, which is the maximum in a BIGINT field. It is safe to assume (given that the people who wrote MySQL probably don t like typing any more than the rest of us) that the data type with the shortest designation, INT , is probably the one the MySQL developers expected to be used the most. In a number field that is not holding bar codes or accounting data of a corporation, the ability of a field to hold a number slightly over 4 billion is arguably adequate.
But why shouldn t you just choose the largest integer range available so you don t ever need to worry about running out of room? The answers are storage and speed.
In an age where pocket calculators hold more memory and computing power than computers that filled entire rooms only 40 years ago did, it is easy to dismiss concerns about storage. Still, it is counterproductive and costly to use two or three times the space to store a field than necessary. Using an extra 2 bytes or 4 bytes doesn t seem like much when you are contemplating only a single record, but multiply that record times the hundreds, thousands, or more times it may appear in its table, and you begin to see a waste of space that you paid for yet are not using.
The other consideration is speed, and it involves something that most computer users don t think much about: read time. Consider for a moment listening to a music CD. If you want to listen to the next track, it takes only a moment for the head to move from track 2 to track 3, for instance; however, if you are at the beginning of a CD and decide you want to listen to a track at the end, it takes a few more moments for the head to move into place and the new music to begin. Movements similar to that happen in your computer hard drive all the time, although at a considerably faster speed than on your CD player. On a computer, the time saved when point A and point B are closer rather than farther happens in increments of time that are small enough to be an abstract concept to most of us.
If you have 1,000 records in a table on your database, and one of the fields is defined as INT , then each field takes up 4 bytes. If, instead, it is defined as SMALLINT ( assuming that the field in question is not likely to exceed 65,535 places), each field takes up half that space. The database would be able to access any record from any other record faster at the 2-byte field size than at the 4-byte field size, simply because there is less ground to cover. An example of such a field is one destined to hold five-digit ZIP code numbers, where the largest number possible is theoretically 99999. Clearly, it will never go over the 65,535 upper limit, so any designation larger than SMALLINT is wasted space.
If you are the only person ever to access your database, then the speed consideration may involve amounts of time so small as to be unnoticeable. On the other hand, if your database exists online in a real-time environment, such as a company-wide intranet or on the Internet, many users may access the data simultaneously . In this case, the small increments of time saved by using the more appropriate field size could lead to less stress in the workplace or increased customer satisfaction. Users in any situation do not like to wait for a computer to give them the response they need.
As noted earlier, you should use TINYINT only when you are sure that the field you are defining will never need to hold a number bigger than 255 (unsigned) or 127 (signed). If you are concerned about storage and speed, choose your data types accordingly . However, most uses for beginners will easily fall into the INT data type, and there will be a clear reason for using one of the other data types. So, when in doubt, use INT .
NUMERIC and DECIMAL Data Types
The DECIMAL and NUMERIC types are interchangeable in MySQL. Both are defined as fixed decimals, which means the field is defined with a specific number of places to the right of the decimal point, as well as the total number of places in the whole field.
Despite the fact that these two types are intended to hold only numeric values, they are stored as characters ; therefore, they should be used to store only numeric data and cannot be used for mathematical purposes. A company s annual report might be printed from accounting data stored in this data type. It creates a kind of safeguard in that the data, once saved in fields defined with this data type, cannot be accidentally manipulated mathematically.
The syntax for the fixed-decimal types is DECIMAL(w,d) or NUMERIC(w,d) , where w equals the total number of digits in the entire number and d equals the number of places to the right of the decimal to display. You can use the type without specifying either of these, specify just one, or specify both. If you don t specify the number of digits in the number or decimal places, the defaults of ten digits without any decimal places will be used. Table 2-3 shows examples of code specifying the DECIMAL and NUMERIC types and what the displayed numbers might look like.
| Syntax | Code | Display | Description |
|---|---|---|---|
DECIMAL( w , d ) DECIMAL(5,2) 123.46 | DECIMAL(5,2) | 123.46 | More decimal places than the field has room for makes the data round up. (5 digits, 2 decimal places) |
NUMERIC( w , d ) NUMERIC(7,4) 123.4567 | NUMERIC(7,4) | 123.4567 | Exact fit. (7 digits, 4 decimal places) |
DECIMAL( w ) DECIMAL(9) 123 | DECIMAL(9) | 123 | No decimal specification defaults to 0 decimal places. (9 digits, 6 padded places to left of number) |
DECIMAL DECIMAL 123 | DECIMAL | 123 | Default is 10 digits, 0 decimal places. (10 digits, 7 padded places to left of number) |
FLOAT and DOUBLE Data Types
The FLOAT and DOUBLE data types are alike, except DOUBLE is stored in twice the amount of bytes, so it has a larger range than FLOAT . These two data types differ from DECIMAL and NUMERIC in that their decimal point is floating rather than fixed.
FLOAT numbers are approximations, which means some numbers might not be able to be stored exactly in the internal format and must be approximated. For instance, the numeric value of pi is an example of a number that must always be an approximation .
FLOAT provides a means of representing noninteger numbers with fractional parts . For example, 9.876 would be represented in storage by the integer 9876 and a 1 to indicate the floating position of the decimal; 987.6 would be represented by storing the integer 9876 and a 3 to indicate its decimal position. This contrasts with fixed-point arithmetic, where decimals defined in each field do not move (there are a fixed number of decimal points in each field, whether or not the value inserted into the field needs a decimal point to be clearly expressed ).
For monetary data or comparisons of strict equality between values, the FLOAT data type is not the best choice; for control of program loops , it is a bad choice.
The FLOAT (or DOUBLE ) data type works well for holding scientific numbers, as you can see from the signed ranges listed in Table 2-4. If UNSIGNED is specified, then negative numbers are disallowed .
| Type | Signed Range |
|---|---|
FLOAT 3.402823466E+38 through 1.175494351E38, | “3.402823466E+38 through “1.175494351E “38, |
DOUBLE 1.7976931348623157E+308 through 2.2250738585072014E308, | “1.7976931348623157E+308 through “2.2250738585072014E “308, |
Text Data Types
Text can be any symbol that is used to communicate a concept. The alphabet comes to mind most readily, but symbols (such as punctuation marks) and numbers can be text, too. The caveat is, as we mentioned earlier, that numbers entered into a text field cannot be used to perform mathematical functions. A common example of text field that includes symbols and numbers as text is an address field. An address may contain a house number in numeric form, with a street name that also can contain a number and often punctuation, such as a period after an abbreviation or a space between words (as in 123 S. Broadway or 987 N. 21 st ).
Any number of text characters (alphabetical, numeric, or symbol characters) in a row is called a string. There are four types of strings that MySQL differentiates with separate data types: CHAR , VARCHAR , TEXT, and BLOB . The first three echo the three criteria of differentiation used for numeric data: fixed-length, variable-length, and long text strings. The fourth type, BLOB , is closely related to TEXT .
CHAR Data Type
The data type CHAR , which is short for character but usually pronounced like the word car, is a fixed-length string. That means the text field has a designated length ”it always takes up that amount of space in the database table record, whether or not the data placed in it is that length. For example, if a field is defined as being 20 characters long, and you place the name Molly (5 characters) in that field, the field is still 20 characters in length. Any remaining space (in this case, 15 characters) is padded to the right of the data until it is the correct fixed length.
This might lead you to believe this field, either printed or displayed onscreen, would look something like Molly but MySQL has designed the CHAR data type so the trailing (or padded) spaces are automatically stripped off on output with the CHAR data type even though the TABLE stores the data type as Molly .
The syntax for this data type is CHAR(w) , where w specifies the field length and can be a number from 1 through 255. If you just specify CHAR , the field defaults to one character.
Any comparisons for sorting CHAR fields are not case-sensitive, by default. If you want case to be considered , you can add the BINARY option: CHAR(w) BINARY . But note that there is a trade-off when you make CHAR case-sensitive: the padded spaces are not stripped off automatically on output. However since you know the padding occurs to the right of the value, you can strip off any extra spaces when printing the data.
VARCHAR Data Type
The syntax for VARCHAR is similar to CHAR ” VARCHAR(w) ”and the value for w can also be any number from 1 through 255. However, unlike with the CHAR type, if the data a VARCHAR field contains is smaller than the specified field length, the field size shrinks to match. If a field is defined as VARCHAR(7) and you enter James in the field, the field in the database table record will be only five characters long. If you enter Crighton, the field will still be only seven characters long, and the data in it will look like Crighto, because it was truncated when it reached the defined upper limit. Like the CHAR data type, VARCHAR is not case-sensitive unless the BINARY option is specified: VARCHAR(w) BINARY .
| Note | The VARCHAR data type actually stores its assigned value + 1. The extra byte is where the record width, represented in the syntax by w , is stored. |
Both these data types have advantages and disadvantages. The VARCHAR type usually will take up less space than the CHAR type, because it varies its length to match the actual length of the data held in its fields. On the surface, this seems like a valid reason for choosing it above the other text types; however, this results in a slower access speed for that table. If all the fields are of a set length (as they are with CHAR ), then the database can go directly to a given field in a table s record by use of a simple mathematical formula. With VARCHAR , the database must look for the field, because the space it takes up can vary from record to record. So, the fixed length of CHAR makes it faster to access, and the variable length of VARCHAR makes it take up less storage space.
Another consideration is that having one VARCHAR field in each of a table s records makes it necessary to treat all the records as if they were of varying length or, as it is sometimes referred to, dynamic . Also, in any varying-length table (a table with one or more varying-length fields in its records), any CHAR longer than three characters will be silently converted to VARCHAR .
A good rule of thumb is that you should put VARCHAR fields only in a table devoted to VARCHAR fields. Also, despite its variable nature, you should still try to define the VARCHAR field as close to a correct upper limit as possible. In other words, don t use a VARCHAR(255) when a VARCHAR(42) will do.
TEXT and BLOB Data Types
The data types TEXT and BLOB are related and most easily explained by contrasting them with one another. Text fields, similar to integer fields, come in different sizes. Table 2-5 shows the amount of bytes each type is stored in and how large a string can be placed in it.
The TEXT data types exist for strings that are longer than 255 characters, which is the upper limit for CHAR and VARCHAR . They are variable in length, so you can think of them as a VARCHAR field that is as long as the upper limit of LONGTEXT . The data types TINYTEXT and VARCHAR can be used interchangeably. If you insert data that is longer than the upper limit of the field, the data is truncated to fit. The TEXT types are not case-sensitive, which means the words Chantel and chantel are exactly the same from MySQL s viewpoint.
The BLOB data type (which has nothing to do with the 1950 s horror classic) is essentially the same as the TEXT data types (see Table 2-5), except for one crucial point: The BLOB type is case-sensitive, which means that the words Vicki and vicki are not the same.
| Data Type | Size | Bytes |
|---|---|---|
| TINYTEXT and TINYBLOB | 2 8 “1 | 255 |
| TEXT and BLOB | 2 16 “1 | 65,535 |
| MEDIUMTEXT and MEDIUMBLOB | 2 24 “1 | 16,777,215 |
| LONGTEXT and LONGBLOB | 2 32 “1 | 4,294,967,295 |
If you put the same set of data containing words that begin with both uppercase and lowercase letters into two tables, one of which has the field defined as a TEXT type and the other as a BLOB type, and then sorted the contents of each, the resulting output would be in different order. Depending on what you do with your database, that small difference may not matter. However, if your sorts come out differently than you expect, the field type of the string you are sorting on may be the culprit.
The VARCHAR BINARY data type can be used interchangeably with TINYBLOB , because the BINARY notation makes VARCHAR case-sensitive.
DATE and TIME Data Types
The DATE and TIME data types are all fairly similar, more like variations on a theme than different types. Both data types use a few basic notations and underlying rules that you need to understand to use them correctly.
The DATE and TIME data types use the following format:
YYYY-MM-DD HH:MM:SS
where:
-
YYYY represents the year
-
MM represents the month
-
DD represents the day
-
HH represents the hour
-
MM represents the minute
-
SS represents the second
The notation M serves as both minute and month, but generally , the range of numbers involved will make the difference between them clear. Also, the use of dash separators for the date and colon separators for the time clearly differentiates between them.
Although MySQL will try to interpret a date written in a different format (for instance, YYYY-DD-MM HH:MM:SS ), it always expects to find the year in the leftmost position. This does not mean that you cannot have the year displayed on the right ( MM-DD-YYYY or DD-MM-YYYY ), but you must store it according to the MySQL expected format. (You will learn how to manipulate the display of date values in Modules 4 and 5.) Storing them in this format makes it easy to group together the same year s data.
If MySQL gets data for a date/time field that does not correlate with the required format, a zero value is inserted. ( 0000-00-00 00:00:00 ). You can also enter a zero value if you do not know the correct data to enter into a specific record; however, afterwards, it is impossible to tell whether the zero value was inserted because of unknown data during input or faulty data.
MySQL does minimal data checking on data input. It checks to see if the month value is between 0 and 12, and if the date value is between 0 and 31. Any further data checking is the responsibility of the user inputting the data. The zero values are allowed as placeholders for unknown data. For example, if you know the month and year of a person s birthday, but not the day, you can enter partial data with zeros for what is missing, and then complete the entry when the missing piece is available.
After the Y2K experience, most users understand why it s best to use a four-digit value for a year. However, MySQL will accept a two-digit value and deal with it using the following rules:
-
Values in the range 0 through 69 will be interpreted and saved as 2000 through 2069.
-
Values in the range 70 through 99 will be interpreted and saved as 1970 through 1999.
Because this rule of thumb will be correct only part of the time, you will save time and effort by adjusting two-digit years into a four-digit format before entering them into your database.
DATETIME, DATE, and TIMESTAMP Data Types
You should use the DATETIME type, rather than the DATE type, only when both the date and time parts are necessary. If all you need is the date, you can save 5 bytes of storage in each record by defining your field correctly. The TIMESTAMP type is slightly different. It gets the current time and places it in the TIMESTAMP field.
Table 2-6 shows the storage size, basic format, and start and end ranges supported by DATETIME , DATE , and TIMESTAMP . MySQL states that this range of dates is what is supported, which means you can enter dates outside the range, but MySQL does not guarantee they will function properly.
| Type | Bytes | Format | Start Date | End Date |
|---|---|---|---|---|
DATE 3 | 3 | YYYY-MM-DD 1000-01-01 | 1000-01-01 | 9999-12-31 |
| DATETIME and TIMESTAMP (version 4.1 and later) | 8 | YYYY-MM-DD HH:MM:SS 1000-01-01 | 1000-01-01 | 9999-12-31 |
| TIMESTAMP (prior to version 4.1) | 4 | YYYYMMDDHHMMSS 1970-01-01 | 1970-01-01 | Sometime in 2037 |
Prior to MySQL version 4.1, TIMESTAMP was stored as a pure number, without any separators or spaces. In version 4.1 and later, it is stored as a string, complete with separators. If you followed the appropriate project for your OS in Module 1, you have installed the latest version of MySQL, version 4.1.1 or above. If you are using an already installed, earlier version of MySQL, or have sought the advice of someone who is using a version prior to 4.1, the behavior of TIMESTAMP can be confusing.
TIMESTAMP and Automatic Updating In the following cases, TIMESTAMP is updated to reflect the latest time a field or fields in a record has been changed in the following manner:
-
An INSERT or LOAD DATA INFILE statement that does not specify a value for the TIMESTAMP column
-
An UPDATE statement that does not specify a value for the TIMESTAMP column and one of the other columns has a change in value
-
A TIMESTAMP value that is set to NULL
TIMESTAMP in MySQL Version 4.1 and Later In version 4.1 and later, TIMESTAMP acts exactly like DATETIME . . . TIMESTAMP acts exactly like DATETIME with one exception. In versions later than 4.1, TIMESTAMP may be run in Maxdb mode, but there is no automatic update function.
In MySQL version 4.1 only, TIMESTAMP does not run in Maxdb mode at all, but if you are not running in Maxdb mode, it does still allow for an automatic update function.
If a statement updates fields with the same data that is already in them, TIMESTAMP will not automatically update, because MySQL ignores the update (for more efficient functioning).
TIMESTAMP Prior to MySQL Version 4.1 Prior to version 4.1, TIMESTAMP was stored as a number, so you could narrow the TIMESTAMP field by defining it with a specific width. The most popular widths, for reasons clearly seen in Table 2-7, are 6, 8, 12, and 14.
| Type and Size | Fomat |
|---|---|
TIMESTAMP(14) YYYYMMDDHHMMSS | YYYYMMDDHHMMSS |
TIMESTAMP(12) YYYYMMDDHHMM | YYYYMMDDHHMM |
TIMESTAMP(10) YYYYMMDDHH | YYYYMMDDHH |
TIMESTAMP(8) YYYYMMDD | YYYYMMDD |
TIMESTAMP(6) YYYYMM | YYYYMM |
TIMESTAMP(4) YYYY | YYYY |
TIMESTAMP(2) YY | YY |
YEAR and TIME Data Types
The YEAR and TIME data types are simple. You use them when that data is all that you require for your database. Table 2-8 shows the basic size and range information for these two data types.
| Type | Bytes | Range | Format |
|---|---|---|---|
YEAR 1 | 1 | 1901 through 2155 | YYYY |
TIME 3 | 3 | “838:59:59 through 838:59:59 | HH:MM:SS |
The YEAR data type can be given a length of either 2 or 4: YEAR (2) or YEAR(4) . As mentioned earlier, the Y2K experience is the classic example of why YEAR(2) should not be used. That being said, if you choose to use it, the value range is similar to the range listed in Table 2-8, but not exactly. With YEAR(2) , 0 through 69 represent the years 2000 through 2069, and 70 through 99 represent the years 1970 through 1999. Illegal YEAR values ”years before 1901 and after 2155 or nonnumeric characters ”are stored as zeros ( 0000 ).
| Note | YEAR() allows input of either numeric or string values. However, if a two-digit YEAR() is defined as a number, its value range is 1 “99 because when zero is specified, it will be interpreted as 0000 instead of 2000. If the YEAR() field is to be used mathematically, the four-digit version avoids this eccentricity. |
The TIME data type has the basic format you might expect, but it also allows a variety of other formats, as shown in Table 2-8. What may be surprising is the large range, which is far outside the normal 24 hours in a day. This range allows for the storage of timing values, such as recording how long something has taken from beginning to end. Once again, if you try to enter a number that is outside this range, the value is stored as the closest upper or lower limit. For instance, 840:39:09 will be stored as 838:59:59 , the upper limit. Invalid TIME values are stored as 00:00:00 .
Defined Group Data Types: ENUM and SET
The ENUM and SET data types both involve a list or series of values defined when the field is defined. However, for ENUM and SET , it is better to think of the data type as the definition of a column of fields. This is because you specify all of the valid data items possible for every field in that table s column when you define the table. After that, only those defined values may be entered into any field in that column.
These two data types both store data as string values (a series of alphanumeric characters perceived as a whole), but they handle the storage differently. ENUM creates an index for the allowable values defined, and then stores the index number, rather than the data itself, in the record field. ENUM allows only one choice from the defined list, which makes it a much smaller value to store.
The SET data type also has defined allowable values, but it stores them as the values themselves . It will allow you to store multiple instances of those values within one field. Therefore, depending on the values and how many of them are stored in a given field, the length of the field may vary considerably. Table 2-9 shows the differences in storage.
| Type | Bytes | Storage Amounts | Defined Values Allowed |
|---|---|---|---|
| ENUM | 1 | Single value, using index number | 0 through 65,535 |
| SET | Up to 8 | Multiple values, using actual values | 0 through 64 |
So, why would you want to use either of these data types? A common use is for drop-down menus on web pages. The list of values you want in your drop-down menu would be the ENUM or SET you define. This approach offers a way to control what people can enter as a value for a particular column of fields in a database table. If you wanted the user to be able to input only one choice from the list of allowable values, you would use the ENUM data type. If you wanted the user to be able to input multiple choices from the list of defined values, you would use the SET data type.
Changes to the ENUM data type require an ALTER TABLE command. It is recommended that you use an ENUM to store only data that is unlikely to change very often. Putting a list of America s 50 states in an ENUM list would be an example of data that does not change very often and from which you might want a user to select only one choice.
ENUM sorts by its index number, so if you desire predictable sorting, you should define the initial list of values in alphabetic order. You can view the index number of an ENUM list by using the syntax < column_name > +0 , as in the following statement:
SELECT <enum_column_name>+0 FROM <table_name>;
The output will give you a list of the index numbers for each record in the table, instead of the contents of the records themselves.
If you had a list of CD titles (64 or fewer) and wanted to instruct the user to choose up to five favorites, SET would be the ideal choice. Each user s record could list that user s input, from zero through five favorites, which would all be stored in a single field. The values in the SET field are comma-separated, making it possible to retrieve them from the database usinga SELECT statement, and then parse the results to separate the data into its individual pieces. Modules 4 and 5 provide more information about handling and manipulating your data.
You now have a basic understanding of the MySQL data types. You ve seen how they work, their uses, and their limitations, as well as why you might want to use them to store specific kinds of data. As stated previously, you do not need to memorize every detail of how they work. You just need to know how and why you might decide on one data type rather than another. When you need information about a specific data type, you can refer to this module.
Progress Check
| 1. | What are the three basic number data types? | |
| 2. | What are the four basic text data types? | |
| 3. | What are the five date and time data types? | |
| 4. | What are the two defined group data types? |
Answers
| 1. | The three basic number data types are INT (TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT), NUMERIC (or DECIMAL ), and FLOAT/DOUBLE . |
| 2. | The four basic text data types are CHAR, VARCHAR, TEXT , and BLOB . |
| 3. | The five date and time data types are TIMESTAMP, DATETIME, DATE, YEAR, and TIME . |
| 4. | The two defined group data types are ENUM and SET . |
EAN: 2147483647
Pages: 109