📖 MySQL Data Types and Functions

Hello world!

In MySQL, data types define the kind of data that can be stored in a column. Understanding these data types is crucial for efficient database design and query optimization. There are five general data types in MySQL.

  • Character (string) types
  • Numeric types
  • Date and time types
  • Large object (LOB) types
  • Spatial types

Character Data types

Character data types store textual information and are used when working with strings, names, and other textual data.

CHAR(int)
Uses fixed maximum length of 255 characters. Ideal for storing data where all entries are expected to be the same length, such as a fixed-length identifier.
VARCHAR (int)
Stores variable-length strings, with a length defined by the specified integer. Suitable for fields where the length of data can vary significantly, such as email addresses or URLs. It can be up to 65535 characters. If you want to save more than a couple of thousand characters, consider using one of the large object types (blob or text).
ENUM and SET
Used to restrict the acceptable values in a character column. Think of drop down lists on a web form.

ENUM is ideal for columns where the value should be restricted to one choice from a predefined list, such as status fields (e.g., 'Pending', 'Completed', 'Cancelled').

  • Stores a single value.
  • Maximum allowed choices is 65,535.
  • Good for mutually exclusive options: Yes/No, State codes, etc.

SET can store 0 or more values from a list of acceptable values.

  • Stores multiple values separated by a comma.

String Functions

I have listed all of the string functions from MySQL - more than in the book. You don't have to learn or use them all, but it's always nice to know what options are available. Each function name is linked directly to the matching MySQL dev docs page.

String Functions List from MySQL Dev Docs
CHAR_LENGTH(str)
Return number of characters in argument
CHAR()

Used to insert control characters into string, line feeds for an address for example.

Return the character for each integer passed. Common control characters are:
CHAR(9) - tab
CHAR(13,10) - Carriage Return, Line Feed

ASCII Table of Codes

CONCAT_WS(sep, str,str,...)
Return concatenate with separator
CONCAT()
Return concatenated string
FORMAT(str,ndec[,locale])
Return a number formatted to specified number of decimal places. Optional locale for formatting decimal point, thousands separator, and grouping between separators.
INSERT(str,pos,len,repl)
Insert a substring at the specified position up to the specified number of characters
LEFT(str,len)
Return the leftmost number of characters as specified
LENGTH(str)
Return the length of a string in bytes
LIKE
Simple pattern matching
LOCATE(substr,str)
Return the position of the first occurrence of substring
LOWER(str)
Return the argument in lowercase
LPAD(str,len,padstr)
Return the string argument, left-padded with the specified string
LTRIM(str)
Remove leading spaces
NOT LIKE
Negation of simple pattern matching
QUOTE(str)
Escape the argument for use in an SQL statement
REGEXP
Pattern matching using regular expressions
REPEAT(str,cnt)
Repeat a string the specified number of times
REPLACE(str,from_str,to_str)
Replace occurrences of a specified string
REVERSE(str)
Reverse the characters in a string
RIGHT(str,len)
Return the specified rightmost number of characters
RPAD(str,len,padstr)
Append string the specified number of times
RTRIM(str)
Remove trailing spaces
SPACE(n)
Return a string of the specified number of spaces
SUBSTR(str,pos,len)
Return the substring starting at pos for len characters. len is optional. If omitted, goes to end of string,
SUBSTRING_INDEX(str,delim,cnt)
Return a substring from a string before the specified number of occurrences of the delimiter
SUBSTRING(str,pos,len)
Return the substring starting at pos for len characters. len is optional. If omitted, goes to end of string,
TRIM(str)
Remove leading and trailing spaces
UPPER(str)
Convert to uppercase

Numeric Data types

Integer types (whole numbers, not numbers with decimal places).
  • BIGINT
  • INT
  • MEDIUMINT
  • SMALLINT
  • TINYINT
  • TINYINT(1) same as BOOL
  • Unsigned and Zerofill integers. Unsigned will not allow for a negative integer. Zerofill to pad an integer with zeroes. INT(4) ZEROFILL would store the value 99 as 0099. Can be useful if all values in a table need to be the same length.
Boolean types

In MySQL, the Boolean data type is simulated using the TINYINT(1) data type. A value of 0 is considered false, and any non-zero value is considered true. This approach allows flexibility in how Boolean logic is implemented, but it's important to be consistent in how you use these values across your database.

Decimal types

Two parameters for maximum length and number of digits to the right of the decimal point. A currency column set to use a data type of DECIMAL(4,2) could only store a maximum dollar value of 99.99. Precision is 4 meaning 4 total digits, and scale is 2 meaning 2 of the digits must be to the right of the decimal point.

Double and Float types

Used to approximate very large or very small numbers. A DOUBLE type can store up to 7 significant digits, a FLOAT type can store up to 15 significant digits. Data is rounded or approximated when the number of significant digits exceeds its type. For example 1234567.89 stored in a FLOAT column would display as 1234568. 7 digits maximum so it rounds the .89 to 8. The DOUBLE data column would store this exactly because a DOUBLE may hold 15 significant digits.

Numeric Functions

Numeric data types are essential for storing and processing numerical information. Understanding the differences between integer, decimal, and floating-point types can help you choose the best data type for your needs, ensuring accurate calculations and efficient storage.

Numeric Functions List from MySQL Dev Docs
ABS()
Return the absolute value
CEILING()
Return the smallest integer value not less than the argument
FLOOR()
Return the largest integer value not greater than the argument
PI()
Return the value of pi
POWER()
Return the argument raised to the specified power
RAND()
Return a random floating-point value
ROUND()
Round the argument
SIGN()
Return the sign of the argument
SIN()
Return the sine of the argument
TRUNCATE()
Truncate to specified number of decimal places

Large Object types

BLOBs and TEXT
Usually used to store video files, images, sounds, xml files, or very large character strings. Large object types are usually used read from and written to by other programming languages.

Converting data

Implicit conversion
When a mathematical operation is performed on a character column, MySQL automatically attempts to convert the data to a numeric format. For example, comparing a date string in the format 'yyyy-mm-dd' to a date column is an implicit conversion. However, if the string contains non-numeric characters that cannot be converted, the operation may fail or produce unexpected results.
Explicit conversion
CONVERT and CAST functions convert data within a SELECT statement from one data type to another. CAST requires an AS keyword CONVERT does not, but CAST is an ANSI-standard function and is therefore more commonly used in MySQL statements.
For most data conversion SELECT statements either of the two keywords may be used.

Conclusion

Mastering MySQL data types and functions is key to designing efficient databases and writing effective queries. By understanding the strengths and limitations of each data type, and knowing how to apply the appropriate functions, you can manipulate and analyze data with greater precision and flexibility.