WDS 3 - Data Types
MSQL supports several different types of data for numbers. The three main categories are numeric, date and time, and character. Most data fits into those categories and but still has certain ways to be stored. Numeric values can be stored in several types: integer, smallint, decimal, numeric, float, real and double precision. Each of these types are for different types of numeric data. MSQL has several different types of storage methods for integers. Using these types can help save storage space if used correctly. For example the TINYINT only uses 1 byte where BIGINT uses 8 bytes. The TINYINT can only store a 3 digit number with a maximum value of 255, and BIGINT can store 20 digits with a maximum value of 18446744073709551615. You wouldn’t want to store a 1 digit number into a MYSQL database with the data type set to BIGINT and at the same time you can’t store a large number in TINYINT because it can only hold 3 digits. If you know your data will always be in a certain range then you can use these types of numeric storage types to help you save space, or in the case of extremely large numbers it is necessary to use the larger data type.
The decimal and numeric values are used when the exact values need to be stored. While the values being stored exactly seems obvious, the difference is that when you use the decimal or numeric forms the numbers are stored in binary rather than as a string. Each type of data storage for numbers has it differences to store all ranges and types of numbers more efficiently.
MSQL also stores strings in many different ways as well. They are CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET. Each of these differs just like the different types of number storage. VARCHAR is used more often than CHAR because of much larger value that VARCHAR can take. Also CHAR stores data differently than VARCHAR. BINARY AND VARBINARY store text in binary form and all comparisons are done by matching the binary values. ENUM is used to only allow specific items to be stored that are predetermined. For example if we set the data type to ENUM and then entered the values in quotes of ‘Yes’,’No’, ‘Maybe’ then the only possible data in the column would be Yes, No and Maybe. This is one way of making sure you get the data you want in the right form you want it.
There are plenty of other data types, all of which store recall and limit the type of data they deal with. It is important to know the data types you are going to be dealing with when making your tables. While many different data types could work for your data it is still important to chose the best one. Refering back to theMYSQL website for describtions of each data type and when you should use a specific data type for your data.
In this picture below we see a database table with 4 fields and 3 different data types circled in red. The circles in blue are the maximum lengths of the data types. The first one is an interger only accepting numbers up to 10 digits. The second and third are VARCHAR which accepts strings of variable lengths. The last is a date stamp that will automaticly incert the date into the column.
