Table of Contents
Mysql Data Types
Mysql Data Types- In this tutorial you learn how to set up detail specifications of MySQL tables. First you learn about the different mysql data types and attributes and then you learn how to use that information to create the tables. You learn what the different mysql data types are and how to assign them to fields. You learn how to set up a field so that it is automatically assigned a value when a row is added, whether that is a MySQL-calculated value to be used as an arbitrary key or a constant value to be used as a default. You also learn advanced functions in phpMyAdmin to create and change tables and how to do the same thing in PHP.
Amazon Purchase Links:
*Please Note: These are affiliate links. I may make a commission if you buy the components through these links. I would appreciate your support in this way!
UNDERSTANDING MYSQL DATA TYPES:
Just as in PHP, MySQL has different data types for the fields. The mysql data types in MySQL are stricter than in PHP and there is not a one-to-one correlation.
Mysql Data Types Strings
There are two types of strings in MySQL. The first is text strings, which have character sets and collations. This is the type of string that you use most often. Text strings are further defined as follows:
- CHAR: This is the character data type. You define exactly how many characters are stored. For example, if you want a field to be six characters long, you define it as CHAR(6). If you pass it data that is less than that, it pads with spaces at the end. If you pass it more, the extra characters are truncated. Whether you are truncating blanks or non-blank characters and what error reporting you have set determines what, if any, errors you see. You can go all the way up to 255 characters. Note that some character sets require more than 1 byte to store some characters. The size limits for the text strings are based on the number of characters, not the number of bytes. Trailing spaces are removed when you retrieve the data.
- VARCHAR: This data type has a variable number of characters. You specify the maximum number of characters, up to 65,535. If you have a field that could contain up to 50 characters but would likely contain less, you define it as VARCHAR(50). There is a little overhead when using VARCHAR rather than CHAR because 1 or 2 bytes are used to store the length. Trailing spaces are not removed when you retrieve the data.
- TEXT: There are four TEXT types — TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. Like VARCHAR, the TEXT types contain a variable number of characters. The difference between the four types is the maximum number of characters. The type defines the maximum number of characters; you do not as shown in below .
The second type of string is binary strings, which have no character sets or collations. Character strings contain text, whereas binary strings contain raw data such as images and other media. The binary types are subdivided in the same way that the text strings are, but the size limits are based on the number of bytes, not the number of characters.
- BINARY: This is the binary data type. You define exactly how many bytes are stored. For example, if you want a field to be exactly 6 bytes long, you define it as BINARY(6). You can go all the way up to 255 bytes.
- VARBINARY: This data type has a variable number of bytes. You specify the maximum number of bytes, up to 65,535. If you have a field that could contain up to 50 bytes but would likely contain less, you define it as VARBINARY(50). There is a little overhead when using VARBINARY rather than CHAR because 1 or 2 bytes are used to store the length.
- BLOB: There are four BLOB types — TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. Like VARBINARY, the BLOB types contain a variable number of bytes. The difference between the four types is the maximum number of bytes. The type defines the maximum number of bytes; you do not. AS shown below.
Numeric Mysql Data Types
As in PHP, numbers that do not have a decimal point are integers. MySQL has different integer types that are based on the size of the integer. Additionally, if the integer is SIGNED that is, has both negative and positive values the range starts in the negative numbers. If the field is flagged as UNSIGNED, the values start at 0 and go twice as high, as you see in below.
You may see these types written with a length such as TINYINT(1) or TINYINT(4). This refers to the number of digits to be displayed. It does not affect the value that is stored or the space needed to store the value. If you need decimals you use either a floating-point data type or a fixed-point data type. The floating-
point types are similar to PHP floating-point types. FLOAT uses 4 bytes of storage and DOUBLE (also called DOUBLE PRECISION or REAL) takes 8. MySQL allows you to specify the total number of digits and the number of digits after the decimal point. So to specify a number between –999.9999 and 999.9999 you use FLOAT(7,4). MySQL rounds the decimal when storing it rather than truncating
it if it is too long. Just as in PHP, floating-point numbers are inexact because of how the computer stores them. You use the exact value data type of DECIMAL (also called NUMERIC) if you need to store exact numbers. Exact values take up more room than the floating-point numbers. Currency is often stored using the decimal format. As with the FLOAT data type, you can specify the total number of digits and the number of digits after the decimal point. DECIMAL(10,2) has a range of –99999999.99 to 99999999.99.
DateTime Mysql Data Types
MySQL stores dates and times in the format of YYYY-MM-DD HH:MM:SS, unlike PHP. Your MySQL server dictates where you can store invalid dates or whether all invalid dates should be converted to zeros.
- DATETIME contains the date and the time. It has a range from the year 1000 through the year 9999.
- DATE contains just the date value.
- You can use TIMESTAMP to automatically contain the initial value or automatically update when something changes on the row. It has a range from 1970 through early 2038. It stores all values as of the UTC time zone.
- TIME displays the time portion of a date or an elapsed time.
- YEAR displays the year. It can be either YEAR(2) or YEAR(4) for two- or four-digit representation of the year. It has a range from the year 1901 through 2155. Two digits between 00 and 69 are converted to 2000 through 2069 and 70 to 99 are converted to 1970 through 1999.
Other Mysql data types
MySQL has a data type ENUM that restricts the field to values from an enumerated list of values. Although you can use numbers as values, it is not recommended because errors can easily occur. Numbers can be misinterpreted as an index of a value instead of the value itself. ENUM(‘small’, ‘medium’, ‘large’)
Although you can put your business logic here, if there’s any chance it might change, it would be better to put the value checking in your program where it would be easier to make changes. MySQL has two other mysql data types that you may come across:
- SET includes zero or more values from a defined list.
- BIT stores data at the bit level using binary values.
USING AUTO_INCREMENT in Mysql Data Types
The primary key for a table should have the following characteristics:
- Not Null
- Not optional
- Never needs to be changed
- Does not violate security policies
In addition, a short simple key that can be retrieved quickly helps performance. It can be difficult to find a data field that meets all of these requirements. For that reason, tables are often given artificial keys — arbitrary keys that have no meaning other than to be a primary key. MySQL supports this policy with the AUTO_INCREMENT attribute. You assign this attribute to a field and MySQL generates a unique sequential number for each new row. You can assign AUTO_
INCREMENT to either an integer or a floating-point data type, though an integer is the most common. Make sure that the data type you choose is large enough to hold the highest number you need. The following snippet of code shows the typical specifications for an artificial primary key. The name of the field is id; it is an integer data type that is unsigned, is a required field, will be automatically
filled by MySQL, and is assigned as the primary key.
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
The table keeps track of the next number to be assigned. It starts with 1 unless you tell it differently when you create the table. When you add the data, if you do not assign a value to id for new rows, or you assign a NULL or 0, a value is automatically assigned. MySQL has a function, LAST_INSERT_ID(), that contains the last AUTO_INCREMENT value. PHP also has a function that can retrieve this number if you need it.
UNDERSTANDING DEFAULTS Mysql Data Types
Default values are used to automatically assign a value to a field when you create a new row and you do not assign a value to that field. If you have a required field (NOT NULL), you should either always assign a value through your program, assign a default, or, in appropriate cases, set it up as AUTO_INCREMENT.
Defaults must be constants. You cannot use MySQL functions, except that you can define a field of the TIMESTAMP data type to default to CURRENT_TIMESTAMP. The following line of code, included when you create a table, creates a field called dept that is up to 20 characters long, is required, and defaults to Office on new rows unless a different value is assigned:
dept VARCHAR(20) NOT NULL DEFAULT ‘Office’,
The following code is for a field called startdate that is a date, is required, and defaults to zeros on new rows unless a different value is assigned:
startdate DATE NOT NULL DEFAULT ‘0000-00-00’
A field does not need to be required to have a default. The following code is for a field called display_number that is an integer with a maximum range of 255 that defaults to 20:
display_number TINYINT UNSIGNED NULL DEFAULT ‘20’
Defaults are not allowed on the TEXT or the BLOB types. If you need to assign a default and the size permits it, create the field as a VARCHAR or VARBINARY data type instead. There are implicit defaults on some mysql data types. These implicit defaults are used if you don’t assign a value or you assign the value of NULL and you have no explicit default. If you have NOT NULL specified and the implicit default has to be used, you get an error depending on the strictness of your
error reporting. These are the implicit defaults:
- Numeric mysql data types default to 0
- Date and time (except for TIMESTAMP) are set to the appropriate version of 0000-00-00 00:00:00
- TIMESTAMP defaults to the current date and time
- Strings default to an empty string
- ENUM defaults to the first value