MySQL Data Type

According to the definition, the database management system aims to manage data. Even a simple SELECT 1 Statement involves an expression evaluate to generate an integer data value. Each data value in MySQL has a type. For example, 37.4 is a number, and "a B c" is a string. Sometimes, the data type is obvious, because the create table statement specifies the type of each column defined as a part of the TABLE, such:

Sometimes, the data type is ambiguous. For example, when a direct value is referenced in an expression, the value is transmitted to a function or the value returned from the function is used, for example:

The INSERT statement completes the following operations, all of which involve data types:
■ Assign the integer 14 to the integer column I n t _ c o l.
■ Pass the string values "a" and "B" to the function C O N C (). C o n c at () returns the string value "a B", which is assigned to the string column s t r _ c o l.
■ Assign the integer 1999 0 115 to the date column date _ c o l. This does not match. Therefore, MySQL will automatically convert the data type. To effectively use MySQL, you must understand how it processes data. This chapter describes the data types that MySQL can process and discusses the problems encountered when processing these data types. The main content is as follows:
■ Common data types, including NULL values.
■ Special data types and attributes that describe each column type. Some column types are quite common, such as CHAR strings. Some types such as AUTO_INCREMENT integer and t I M E S TAMP date have special performance and should be understood to avoid errors.
■ Appropriately select the column type of the table. When creating a table, it is important to know how to select the best type for your purposes, and select a type when several types can be used for the value you want to store.
■ Expression evaluate rule. MySQL provides many operators and functions that can be used for expressions to search, display, and process data. The expression evaluate rules include the type conversion rules. When one type of value is used for another type of value, the type conversion rules are required. It is important to understand when to perform type conversion and how to perform conversion. Some conversions are meaningless and produce incorrect values. Assign "13" to the integer column and the result is 13, but assign "a B c" to the column to get 0, because "a B c" is not a number. Even worse, if you do not know the value conversion, it may bring a great risk, for example, when you plan to operate only a few rows, all rows in the table may be updated or deleted. Appendix B and appendix C provide more information about MySQL column types, operations, and functions.
2.1MySQL Data Type
MySQL has several data types, which are described below.
1. Value
A value is a value like 48 or 193.62. MySQL supports values that are interpreted as integers without decimals) or floating-point numbers with decimals. Integers can be expressed in decimal or hexadecimal format. An integer is composed of numerical sequences. An integer expressed in hexadecimal form is composed of "0 x" followed by one or more hexadecimal numbers "0" to "9" and "a" to "f. For example, 0x0a is 10 in decimal format, while 0 x ffff is 6 5 5 3 5 in decimal format. Hexadecimal numbers are case-insensitive, but the prefix "0 x" cannot be "0 X ". That is, 0x0a and 0x0A are both valid, but 0X0a and 0X0A are not valid. A floating point number consists of an Arabic sequence, a decimal point, and another arabic sequence. The two Arabic numbers can be empty, but cannot be empty at the same time. MySQL supports scientific notation. Scientific notation is represented by an integer or floating point number followed by "e" or "E", a symbol "+" or "-"), and an integer index. 1.34E + 12 and 43.27e-1 are the numbers expressed by legal scientific notation. 1.34E12 is not legal because the symbols before the index are not given. The "e" before the index is also a valid hexadecimal number, so it may be wrong. You can place a negative value "-" before a value to indicate a negative value.
2. character) the string value is a value such as "Madison, Wi s c o n s I n" or "patient shows improvement. You can enclose string values either in single or double quotes. Several escape sequences can be used in a string to indicate special characters, as shown in Table 2-1. Each sequence starts with a backslash (\), indicating that it is temporarily different from the common character interpretation. Note that NUL bytes are different from NULL values. NUL is a zero-value byte, while NULL is NULL.

To enclose a pair of quotation marks, you can choose from the following three options:
■ If the string is enclosed by the same quotation marks, double quotation marks are required in the string. For example:

■ If the string is enclosed by another pair of quotation marks, double quotation marks are not required. For example:

■ It is represented by a backslash. This method is not used to enclose single quotation marks or double quotation marks. For example:

In the string environment, the string value can be specified using a hexadecimal constant. The syntax is the same as the preceding numeric value, but each hexadecimal number is considered as an ASCII code and converted to a character. The result is used as a string. For example, if 0x6 16 2 6 3 is used as a string, it is "a B c ".
3. Date and Time Value
Date and time are values such as 1999-0 6-17 or 12: 3 0: 4 3. MySQL also supports combination of dates and times, for example, "12:30:43 ". Pay special attention to the fact that MySQL represents the date in the order of year-month-day. MySQL beginners are usually surprised at this point. In fact, this is the standard format of ansi SQL. You can use the DATE _ f o r m at () function to display the DATE value in any form. However, the default display format is year first, and the input value must be year first.
4. NULL Value
NULL is a value of "no type. It used to indicate "no value", "unknown value", "lost value", "overflow value", and "no above value. You can insert NULL values into a table and retrieve them from the table to test whether a value is NULL. However, you cannot perform arithmetic operations on NULL values. If you perform arithmetic operations on NULL, the result is NULL ).