Shulou(Shulou.com)05/31 Report--

This article mainly introduces several data types in mysql, which can be used for reference by interested friends. I hope you will gain a lot after reading this article.

1. Data type of mysql

There are several data types in mysql:

(1) numerical www.2cto.com

The value is a value such as 32 or 153.4. Mysql supports scientific notation, which is represented by an integer or floating point number followed by "e" or "e", a symbol ("+" or "-"), and an integer index. Both 1.24e+12 and 23.47e-1 are legal scientific representations of numbers. And 1.24 trillion is not legal because the sign before the index is not given.

A floating point number consists of an integer part, a decimal point, and a decimal part. The integer part and the decimal part can be empty respectively, but not both.

The value can be preceded by a negative sign "-" to indicate a negative value.

(2) character (string) type

Character types (also known as character strings, or strings for short) are characters such as "hello, world!" Or a value like "a murder caused by a steamed bread", or a value like the phone number 87398413. String values can be enclosed in either single or double quotation marks. Beginners often can't tell the difference between a value of 87398143 and a string of 87398143. They are all numbers. Why should one use numeric type and the other should use character type? The key is that the numeric 87398143 is involved in the calculation, for example, it is a total payment in finance, while the character 87398143 does not participate in the calculation, but only represents the phone number, as well as street numbers, house numbers, and so on. They don't participate in the calculation.

(3) date and time type

The date and time are values such as 2006-07-12 or 12:30:43. Mysql also supports date / time combinations, such as "2006-07-12 12:30:43".

(4) null value

Null represents an unknown value. For example, if the mailing address in the form is not clear and left blank, this is the null value.

We use the create table statement to create a table (see the previous section) that contains the definition of the column. For example, we created a joke table earlier with two columns, content and writer:

Create table (content varchar (20) not null, writer varchar (10) null)

The syntax for defining a column is as follows:

Col_name col_typy [col_attributes] [general_attributes]

Www.2cto.com

The column name is given by col_name. Column names can contain up to 64 characters, including letters, numbers, underscores, and dollar signs. The column name can begin with any legal symbol (including numbers) in the name. However, column names cannot be composed entirely of numbers, as that may make them inseparable from the data. Mysql retains words such as select, delete, and create, which cannot be used as column names, but function names such as pos and min can be used.

The column type col_type represents a specific value that the column can store. The column type specifier can also represent the maximum length of the value stored in the column. For some types, their length can be clearly indicated by a numerical value. Other values have lengths implied by the type name. For example, char (10) explicitly specifies a length of 10 characters, while the tinyblobvalue implies a maximum length of 255 characters. Some type descriptors allow you to specify the maximum display width (that is, how many characters are used to display the value). The floating point type allows you to specify the number of decimal places, so you can control the precision of the floating point number.

You can specify optional type description properties after the column type, as well as more common properties. Properties act as modifiers for types and change the way they handle column values. There are the following types of properties:

(1) dedicated properties are used to specify columns. For example, the unsigned attribute is only for integers, while the binary attribute is only used for char and varchar.

(2) General properties can be used for any column except for a few columns. You can specify null or not null to indicate whether a column can hold null. You can also use default,def_value to indicate that the column can be assigned a value of def_value when a new row is created but the value of the column is not explicitly given. Def_value must be a constant; it cannot be an expression or reference other columns. You cannot specify default values for blob or text columns.

If you want to give specific properties for multiple columns, you can specify them in any order, as long as they follow the column type and precede the generic properties. Similarly, if you need to give multiple common properties, you can give them in any order, as long as you put them after the column type and the column-specific properties that may be given.

2. Column (field) type of mysql

Each table in consists of one or more columns (fields). When you create a table with the create table statement, specify a type for each column (field). The type of column (field) is more refined than the data type, and it accurately describes the types of values that a given table column (field) may contain, such as whether there is a decimal number and whether there is a lot of text.

2.1 Numeric column types have integer and floating point values, as shown in Table 1. Integer column types can be signed or unsigned. There is a special attribute that allows integer column values to be generated automatically, which is useful for applications that require unique sequences or identification numbers. Www.2cto.com

Types

Description

Tinyint is a very small integer

Smallint smaller integer

Mediumint medium size integer

Int standard integer

Bigint larger integer

Float single precision floating point number

Double double precision floating point number

Floating-point number of a string in decimal

Table 1: numeric column types

The name and range of values for each numeric type are shown in Table 2.

Type description

Value range

Tinyint [(m)] has symbolic values:-128to127(-27 to 27-1) No symbolic values: 0 to 255( 0 to 28-1)

Smallint [(m)] has symbolic values:-32768 to 32767 (- 215 to 215-1) No symbolic values: 0 to 65535 (0 to 21 6-1) www.2cto.com

Mediumint [(m)] has symbolic values:-8388608 to 8388607 (- 22 3 to 22 3-1) No symbolic values: 0 to 16777215 (0 to 22 4-1)

Int [(m)] has symbolic values:-2147683648 to 2147683647 (- 231 to 231-1) No symbolic values: 0 to 4294967295 (0 to 232-1)

Bigint [(m)] has symbolic values:-9223372036854775808 to 9223373036854775807 (- 263 to 263-1)

Unsigned values: 0 to 18446744073709551615 (0 to 264 # 1)

The minimum non-zero value of float [(m, d)]: ±1.175494351e-38

The minimum non-zero value of double [(m) d] is ±2.2250738585072014e-308.

Decimal (m, d) is variable; the range of values depends on m and d

Table 2: range of values for numeric column types

The amount of storage required for various types of values is shown in Table 3.

Type description

Storage requirement

Tinyint [(m)] 1 byte

Smallint [(m)] 2 bytes

Mediumint [(m)] 3 bytes

Int [(m)] 4 bytes

Bigint [(m)] 8 bytes

Float [(m, d)] 4 bytes

Double [(m, d)] 8 bytes

Decimal (m, d) m bytes (mysql

3.23)

Table 3: storage requirements for numeric column types www.2cto.com

Mysql provides five integers: tinyint, smallint, mediumint, int, and bigint. Int is the abbreviation of integer. These types are different in the range of representable values. Integer columns can be defined as unsigned to disable negative values; this makes the range of values for the column above 0. Different types of storage requirements are also different. Types with a wide range of values require a larger amount of storage.

Mysql provides three floating-point types: float, double, and decimal. Unlike integers, floating-point types cannot be unsigned, and their range of values is different from integers, not only in that these types have a maximum value, but also a minimum non-zero value. The minimum value provides a measure of the corresponding type of accuracy, which is very important for recording scientific data (of course, there are negative maximum and minimum values).

When you select a numeric type, you should consider the range of values you want to represent, just select the smallest type that can cover the range of values you want to take. Choosing a larger type wastes space, makes the table grow unnecessarily, and is not as effective as selecting a smaller type. For integer values, tinyint is most appropriate if the range of data values is narrow, such as the age of the person or the number of siblings. Mediumint can represent millions of values and can be used for more types of values, but it is expensive to store. Bigint has the largest range of values of all integers and requires twice as much storage space as the second-largest integer int type, so it is used only when it is really needed. For floating-point values, double takes up twice the space of float. Unless there is a special need for high-precision or wide-range values, you should generally use float types that use only half the storage cost to represent data.

When defining an integer column, you can specify the optional display size m. If so, m should be an integer from 1 to 255. It represents the number of characters used to display the values in the column. For example, mediumint (4) specifies a mediumint column with a four-character display width. If an integer column with no explicit width is defined, it will be automatically assigned a default width. The default value is the length of the longest value for each type. If the printable representation of a particular value requires more than m characters, the full value is displayed; the value is not truncated to fit m characters. Www.2cto.com

For each floating point type, you can specify a maximum display size m and decimal place d. The value of m should be from 1 to 255. The values of d can be 0 to 30, but should not be greater than m-2 (if you are familiar with odbc terminology, you will know that m and d correspond to "precision" and "decimal places" of odbc concepts). M and d are optional for both float and double, but are required for decimal. In the options m and d, if they are omitted, the default values are used.

2.2 string column types

Mysql provides several string types for storing character data, which are as follows:

Type name

Description

Char fixed length string

Varchar variable length string

Tinyblob very small blob (binary large objects)

Blob Little blob

Medium blob in mediumblob

Longblob big blob

A very small text string in tinytext

Text small text string

Mediumtext medium text string

Longtext large text string

Enum enumeration; columns can be assigned to an enumeration member

Set collection; columns can be assigned to multiple collection members

Table 4: string column types

The following table shows the types of string value columns defined by mysql, as well as the maximum size and storage requirements for each type. For column types with variable length, the amount of storage occupied by the values of each row is different, depending on the length of the values actually stored in the column. This length is represented by l in the table. Www.2cto.com

Type description

Maximum size

Storage requirement

Char (m) m bytes m bytes

Varchar (m) m bytes l + 1 bytes

Tinyblob, tinytext 28-1 byte l + 1 byte

Blob, text 216-1 byte l + 2 byte

Mediumblob, mediumtext 224-1 byte l + 3 byte

Longblob, longtext 232-1 byte l + 4 byte

Enum ("value1", "value2",...) 65535 members 1 or 2 bytes

Set ("value1", "value2",...) 64 members 1, 2, 3, 4 or 8 bytes

Table 5: serial type maximum size and storage requirements

The additional bytes required other than l are the number of bytes required to store the length of the value. Mysql handles values of variable length by storing the contents of the value and its length. These extra bytes are unsigned integers. Note the correspondence between the maximum length of a variable length type, the number of additional bytes required for this type, and unsigned integers that occupy the same number of bytes. For example, a mediumblob value might be up to 224-1 bytes long and require 3 bytes to record its result. The maximum unsigned value for a 3-byte integer type mediumint is 224-1. This is no accident. Www.2cto.com

2.3 date-time column type

Mysql provides several column types of time values: date, datetime, time, timestamp, and year. The following table shows the types that mysql provides for defining stored date and time values, and gives a range of legal values for each type.

Type name

Description

Date value expressed in date "yyyy-mm-dd" format

Time value expressed in time "hh:mm:ss" format

Datetime "yyyy-mm-dd hh:mm:ss" format

Timestamp value represented by timestamp "yyyymmddhhmmss" format

Year value in year "yyyy" format

Table 6: date-time column types

Type name

Value range

Storage requirement

Date "1000-01-01" to "9999-12-31" 3 bytes

Time "- 838 rig 59" to "838 rig 59" 3 bytes

Datetime "1000-01-01 00:00:00" to "9999-12-31 23:59:59" 8 bytes

Timestamp 19700101000000 to 2037 sometime 4 bytes

Year 1901 to 2155 1 byte www.2cto.com

Table 7: value range and storage requirements of day-ahead time column types

Here's an example:

Create table student (name varchar 20) not null, chinese tinyint (3), maths tinyint (3), english tinyint (3), birthday date)

This example creates a student table with name fields, character type columns, and null (null values) not allowed. There are three integer type columns, chinese, maths, and english. There is also a birthday date type column.

Thank you for reading this article carefully. I hope the article "there are several data types in mysql" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.