Many professional web sites use databases to store and organize data that their websites may need to use. Databases were one of the very first uses of computers, and their ability to sort and store data is very useful. In relation to web sites, it's important that the site designer knows what kinds of information needs to be stored, and how it can be stored into a database. In particular, the designer must know about the data types supported in MySQL.
Data types are the different ways that a computer defines information. Deep down, all data on a computer is either a 0 or a 1. A computer can preface a string of ones and zeros with a data type, so it knows what to display. There are 3 basic kinds of data types: characters, numbers, and dates.
Character data types: Also known as String types. (If you're a programmer this shold make sense.) These data types are meant to hold words, phrases or text. The two most basic types are CHAR and VARCHAR. CHAR is meant to store single characters or short words, at it has s storage limit of 255 characters. VARCHAR has the same limit in earlier versions of MySQL, but after 5.0.3 was expanded to 65,535. The main difference between CHAR and VARCHAR is how they are stored. A CHAR's length must be determined at the creation of the table, while a VArCHAR's length can vary. The advantage to CHAR is that it uses less storage space on average. However, the length must be limited in advance and requires the same amount of storage space regardless of the length of the actual data stored.
BINARY and VARBINARY are very similar to CHAR and VARCHAR, except that they are treated as sets of binary data, instead of as characters. However, each BINARY and VARBINARY will have a character set assigned to it, making them useful for storing text that is not in a default font or format, such as foreign alphabets.
TEXT and BLOB type: Text is meant to store large amounts of text, and is usualy the best choice for storing meggages. BLOB is used for binary files, such as images, mp3 files and such. The size limit for each of these types depends more on the amount of storage memory the machine has, and less on MySQL itself.
ENUM type: ENUMs are similar to VARCHARs, except that the value stored must be of one pre-detrmined by the database table. For example, if an entry should contain a day of the week, an ENUM could be used that constrains all the possible entries to the seven days in the week.
SET type: SETs are essentially a collection of various values in a single database entry. SETs are best used when lists need to be compared to other lists. For example, a car shop may list all the cars owned by a single person in a SET, or each car may have its work orders in a SET. This lets the user compare cars with similar work orders, or owners with similar models, without needing to expand the database tables.
Time types: Often time is imprtant to information, so MySQL has devotes some types to better store dates and time. The types are as follows:
DATE and DATETIME are very similar. DATE will store a single date in YYYY-MM-DD format, DATETIME is slightly more accurate with a YYYY-MM-DD HH:MM:SS format. Both can store a very wide range of dates. The range is limited from 1000-01-01 00:00:00 to 9999-12-31 23:59:59. This is to keep all dates within the normal range, but the variant ALLOW_INVALID_DATES will let you enter any kind of date.
TIMESTAMP, TIME and YEAR: TIMESTAMP is rather straight forward; it sinmply records the time of the system the database is in. This is handy for recording when entries are made. TIME is usualy a simple HHH:M:SS format, with a range to -838:59:59 to 838:59:59. TIME is used to represent either a specific time, elapsed time, or time remaining. YEAR is a single number, either 4 digits ranging from 1901 to 2155, or 2 digits from 00 to 99, though these are interpereted within the rage 1970 to 2069.
All of the time types can varyis exact format, though the data stored in each type is the same.
Number Types: Databases store a lot of numbers, so there are many different kinds of types.
Integers: There are several sizes of integer types, each useful when you know the possible range of numbers to be recorded. Integers are unique that they must contain a whole number, no decimals. Integers can use the least amount of resources, and is useful when counting a number of items or giving ID numbers. Integers normaly can be positive or negative, or limited to positive numbers.
Floating point: Sometimes a decimal number may need to be recorded, such as an amount of money or distance. FLOAT and DOUBLE data types store numbers with decimal points. Both are very similar, though a FLOAT cannot contain as much prescision as a DOUBLE.
There are many more different types for numbers, each with their own range, level of perscision, and storage requirements. INT, and FLOAT will suffice for more purposes, though. All of the data types, not just numbers, have several variants that alter the apperance or format of data. While there are many tools available for those deeply involved in databases, a knowledge of the basic types is fine for beginning developers.
Reference: http://dev.mysql.com/doc/refman/5.0/en/data-types.html
Contact Me | Policies | Colophon
© 2009, Jonathan Dunstan, All Rights Reserved.