MySQL Data Type

Explain the difference between CHAR and VARCHAR column types.

Char is a data type with fixed length. On the other hand, VARCHAR is of variable length. The size of the columns becomes the storage size when CHAR is used. On the other hand, due to the varied length of VARCHAR, the size of the data entered becomes the storage size. For e.g if a string SAMPLE is stored using data type as CHAR(10), the column will contain SAMPLE with 4 trailing spaces. While id the data type is VARCHAR(10), the column will just have SAMPLE.

Explain the difference between BLOB and text column types.

BLOB is Binary large object whose values being binary are case sensitive when comparing or sorting. TEXT values are not and hence the comparison and sorting is not done in a case sensitive way.

Explain ENUM and SET column type.

- Using Enum can restrict the number of values that can be allowed to go inside a table. If there is an ENUM for Colors ENUM(‘blue’,’yellow’,’green’) I can use an insert statement like Insert colors values (‘red’).
- Using SET as a column type allows values of that column to be from a set of values.

Example: If a column is specified as SET(‘a’,’b’) NOT NULL, the column can take one of the following values:-

‘ ‘
‘a’
‘b’
‘a, b’

The maximum number of values that can be set is 64. Duplicate values in the definition cause a warning. Trailing spaces are deleted from this definition.

Explain the 3 main categories of data types of MySQL.

1. Numeric – MySql supports all Numeric data types like INTEGER, SMALLINT, DECIMAL, FLOAT, REAL etc. Integer data types are usually represented as INT(4).integer types can have an optional UNSIGNED ATTRIBUTE if only non negative numbers need to be allowed. FLOAT is used to represent approximate values. DECIMAL and NUMERIC are used to store exact values. INT takes 4bytes of storage.
2. Date and Time – The types used to represent date and time in MySql are – DATETIME, DATE, TIMESTAMP, TIME and YEAR. TIMESTAMP has an automatic updating behaviour. MySql gives a warning if an illegal date is attempted to be entered. Values for a given date and time are retrieved in a standard format. However, it can change as specified by the user. Zero” date or time values used through MyODBC are converted automatically to NULL
3. String – The String types supported in MySql are – CHAR, VARCHAR, TEXT, ENUM, BINARY, SET. Char and Varchar are used to store characters. Example – char(20) can store 20 characters. Char have a fixed specified length while Varchar is variable. BINARY is used to store binary strings. Strings that have no sorting, character set are binary strings. TEXT is used for strong a large string of data. ENUM is used when only a list of values needs to be allowed. SET can have zero or more values chosen from a list of allowed values specified when the table is created.