MySQL data type

Source: Internet
Author: User
Tags date format arithmetic expression functions include mysql numeric value
mysql| Data | Data type MySQL data type
By definition, the purpose of a database management system is to manage data. Even a simple SELECT 1 statement involves evaluation of an expression to produce an integer data value. There are types for each data value in MySQL. For example, 37.4 is a number, and "a B C" is a string. Sometimes, the type of the data is obvious, because when you use the CREATE TABLE statement, you specify the type of each column that is defined as part of the table, such as:

Sometimes, the data type is ambiguous, such as when referring to a direct value in an expression, passing the value to a function, or using the value returned from the function, such as:

INSERT statements complete the following operations, all of which involve data types:
Assigns an integer value of 14 to the integer series I n t _ c o L.
Pass a string value "a" and "B" to the function C O N C at (). C o N C at () returns the string value "a B", which is given a string of s t r _ c o L.
Assign an integer value of 1999 0 115 to the date column dates _ c o L. And that's not a match, so MySQL will automatically convert data types. To effectively use MySQL, you must understand how it handles data. This chapter describes the types of data that MySQL can handle and discusses the issues that arise when dealing with these data types, mainly as follows:
Common data types, including null values.
Special data types, and properties that describe each column type. Some column types are fairly common, such as char string types. And some such as auto_increment integral type and T I M E S tamp date type, its performance is very special, should be understood to avoid error.
Select the column type of the table appropriately. When creating a table, it is important to understand how to choose the best type for your own purposes and to choose a type when several types can be used for the value you want to store.
An expression evaluation rule. MySQL provides a number of operators and functions that can be used in expressions to retrieve, display, and process data. The rules for evaluation of an expression include a type conversion rule that requires a type conversion rule when a value of one type is used for a value of another type. It is important to understand when to type conversions and how to convert them, and some conversions are meaningless and produce error values. Assign the string "13" to the integer sequence result to a value of 13, but assign a string "a B C" to the column to get a value of 0, because "a B C" is not a number. Worse, if you make comparisons without understanding the conversion of values, there is a risk that all rows in the table may be updated or deleted when you intend to operate on only a few lines. Appendix B and Appendix C provide more information on MySQL column types, operations, and functions.
2.1MySQL data type
MySQL has several data types, which are described separately below.
1. Numerical Value
The numeric value is such as 48 or 193.62. MySQL support describes the value of an integer (without a decimal part) or a floating-point number (with a decimal part). Integers can be represented in decimal or hexadecimal form. Integers are made up of numeric sequences. Integers in the form of 16 are composed of "0 x" followed by one or more hexadecimal digits ("0" to "9" and "a" to "F"). For example, 0x0a is 10 in decimal, and 0 x FFFF is 6 5 5 3 5 in decimal. Hexadecimal digits are case-insensitive, but their prefix "0 x" cannot be "0 x". Both 0x0a and 0x0a are legal, but 0x0a and 0x0a are not legal. A floating-point number consists of a sequence of Arabic numerals, a decimal point, and another sequence of Arabic numerals. A sequence of two digits can be empty, but not 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 exponent. 1.34E+12 and 43.27e-1 are the number of legal representations of scientific representations. And 1.34E12 is not legal, because the sign before the exponent is not given. The "E" before the exponent is also a valid hexadecimal number, so it is possible to make a mistake. You can put a minus sign "-" to indicate a negative value before the number.
2. (character) string value strings are values such as "Madison, Wi s c o n s i n" or "Patient shows improvement". You can enclose string values in both single quotes and double quotes. You can use several escape sequences in a string to represent special characters, as shown in table 2-1. Each sequence begins with a backslash ("\"), indicating that the temporary differs from the usual character interpretation. Note that the NUL byte differs from the null value; NUL is a 0-valued byte, and null is no value.

To include a quotation mark in a string, there are three choices:
If the string is enclosed in the same quotation mark, enclose the quotation mark in the string where it is required. Such as:

If the string is enclosed in another quotation mark, you do not need to double write the corresponding quotation mark. Such as:

represented by a backslash; This method does not take into single or double quotes that are used to enclose a string. Such as:

In the context of a string, a hexadecimal constant can be used to specify a string value. The syntax is the same as the numeric value described previously, but each pair of hexadecimal digits is treated as ASCII code and converted to characters, and the result is used for strings. For example, 0 x 6 16 2 6 3 as a string is "a B C".
3. Date and Time values
The date and time are values such as "1999-0 6-17" or "12:3 0:4 3". MySQL also supports a combination of date/time, such as "1999-06-17 12:30:43". Pay special attention to the fact that MySQL represents a date in the order of year-month-day. MySQL beginners are usually surprised by this, but this is the standard format for ANSI SQL. You can display a date value in any form using the date _ F O R M at () function, but the default display format displays the year first, and the input value must first give the year.
4. NULL value
NULL is a "no type" value. It used to mean "no value", "Unknown Value", "Lost Value", "Overflow value" and "No above value". You can insert null values into a table, retrieve them from a table, test whether a value is null, but you cannot perform arithmetic operations on null values (the result is null if you perform arithmetic operations on null).

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.