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

This article mainly explains "what are the data types of Mysql". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what are the data types of Mysql".

Varchar takes up 2 bytes for each ASCII character and only 2 bytes for a Chinese character.

Char occupies 1 byte of English (ASCII) characters and 2 bytes of Varchar for a Chinese character without filling with blanks, such as varchar (100), but its value is only "qian", then its value is "qian" and char is different, such as char (100), its value is "qian", but in fact it is "qian" (there are 96 spaces after qian, that is, fill it for 100 bytes).

Because char is of fixed length, it will be much faster than varchar! But the program is a little more troublesome to deal with, using functions such as trim to remove the spaces on both sides!

Ntext

The maximum length of variable length Unicode data is 2301 (1073741823) characters. The storage size is twice the number of characters entered (in bytes). The synonym for ntext in SQL-92 is national text.

Text

The maximum length of variable-length non-Unicode data in the server code page is 231-1 (2147483647) characters. When the server code page uses double-byte characters, the storage capacity is still 2147483647 bytes. The storage size may be less than 2147483647 bytes (depending on the string).

Bigint: integer data from-2 ^ 63 (- 9223372036854775808) to 2 ^ 63-1 (9223372036854775807), with a storage size of 8 bytes.

Int: integer data from-2 ^ 31 (- 2147483648) to 2 ^ 31-1 (2147483647) with a storage size of 4 bytes.

Smallint: integer data from-2 ^ 15 (- 32768) to 2 ^ 15-1 (32767) with a storage size of 2 bytes.

Tinyint: integer data from 0 to 255 with a storage size of 1 byte.

Integer data of bit:1 or 0, with a storage size of 1 byte.

Unicode data

In Microsoft ®SQL Server ™2000, traditionally non-Unicode data types allow the use of characters defined by a specific character set. The character set was selected when installing SQL Server and cannot be changed. Using the Unicode data type, columns can store any character defined by the Unicode standard, including all characters defined by different character sets. Unicode data types require twice as much storage space as non-Unicode data types.

Unicode data is stored using the nchar, varchar, and ntext data types in SQL Server. These data types can be used for columns that store characters from multiple character sets. The nvarchar type is used when each item in the column contains a different number of Unicode characters (up to 4000). The nchar type is used when the items in the column are of the same fixed length (up to 4000 Unicode characters). The ntext type is used when any item in the column exceeds 4000 Unicode characters.

Indicates that the Unicode data type of SQL Server is based on the country character data type in the SQL-92 standard. SQL-92 uses the prefix character n to identify these data types and their values.

1. Data type

A data class is an attribute of data that represents the type of information that the data represents. Any computer language defines its own data type. Of course, different programming languages have different characteristics, and the types and names of the defined data types are more or less different. SQL Server provides 25 data types:

Binary [(N)]

Varbinary [(N)]

Char [(N)]

Varchar [(N)]

Nchar [(N)]

Nvarchar [(N)]

Datetime

Smalldatetime

Decimal [(p [, s])]

Numeric [(p [, s])]

Float [(N)]

Real

Int

Smallint

Tinyint

Money

Smallmoney

Bit

Cursor

Sysname

Timestamp

Uniqueidentifier

Text

Image

Ntext

(1) binary data type

Binary data includes Binary, Varbinary and Image.

The Binary data type can be either Binary or variable length.

Binary [(N)] is n-bit fixed binary data. Where the value of n ranges from 1 to 8000. The size of its storage blocks is n + 4 bytes.

Varbinary [(N)] is n-bit variable length binary data. Where the value of n ranges from 1 to 8000. Its storage size is n + 4 bytes, not n bytes.

The data stored in the Image data type is stored as a bit string, is not interpreted by SQL Server, and must be interpreted by the application. For example, an application can store data in the Image data type using BMP, TIEF, GIF, and JPEG formats.

(2) character data type

The types of character data include Char,Varchar and Text.

Character data is data made up of any combination of letters, symbols, and numbers.

Varchar is variable-length character data whose length does not exceed 8KB. Char is fixed-length character data with a maximum length of 8KB. ASCII data that exceeds 8KB can be stored using the Text data type. For example, because Html documents are all ASCII characters and are generally longer than 8KB, these documents can be stored in SQL Server with Text data types.

(3) Unicode data type

Unicode data types include Nchar,Nvarchar and Ntext.

In Microsoft SQL Server, traditional non-Unicode data types allow the use of characters defined by a specific character set. During SQL Server installation, you are allowed to select a character set. Using the Unicode data type, any character defined by the Unicode standard can be stored in the column. In the Unicode standard, all characters defined in various character sets are included. Using the Unicode data type, you can overcome twice the size of the garbage occupied by using a non-Unicode data type.

In SQL Server, Unicode data is stored as Nchar, Nvarchar, and Ntext data types. Columns stored using this character type can store characters in multiple character sets. When the length of a column changes, you should use the Nvarchar character type, which can store up to 4000 characters. The Nchar character type should be used when the length of the column is fixed, and again, a maximum of 4000 characters can be stored. When using the Ntext data type, the column can store more than 4000 characters.

(4) date and time data types

Date and time data types include Datetime and Smalldatetime.

The date and time data type consists of valid dates and times. For example, valid date and time data include "4-01-98 12 PM 15 00 00 PM" and "1 14 28 14 29 15 15 01 AM 8-17-98". The first data type is the date, the time is the last, the data type is immediately before, and the date is after. In Microsoft SQL Server, when the date and time data types include Datetime and Smalldatetime, the stored date range begins on January 1, 1753, and ends on December 31, 9999 (each value requires 8 storage bytes). When using the Smalldatetime data type, the stored date range is from January 1, 1900 to December 31, 2079 (each value requires 4 storage bytes).

The format of the date can be set. The command to format the date is as follows:

Set DateFormat {format | @ format _ var |

Where format | @ format_var is the order of dates. Valid parameters include MDY, DMY, YMD, YDM, MYD, and DYM. By default, the date format is MDY.

For example, when Set DateFormat YMD is executed, the date is in the form of year, month, and day; when Set DateFormat DMY is executed, the date is in the form of day, month and year.

(5) Digital data type

Digital data contains only numbers. Numeric data types include positive and negative numbers, decimals (floating point numbers), and integers.

Integers consist of positive and negative integers, such as 39, 25, 0-2, and 33967. In Micrsoft SQL Server, the data types stored in integers are Int,Smallint and Tinyint. The range of data stored by the Int data type is greater than that of the Smallint data type, while the range of data stored by the Smallint data type is greater than that of the Tinyint data type. Using Int data to store data ranges from-2147 483,648 to 2147 483,647 (each value requires 4 bytes of storage). When using the Smallint data type, the range of stored data ranges from-32 768 to 32 767 (each value requires 2 bytes of storage). When using the Tinyint data type, the range of stored data is from 0 to 255 (each value requires 1 byte of storage space).

The data types of accurate Lou data in SQL Server are Decimal and Numeric. The storage space occupied by such data is determined according to the number of digits after the number of digits of the data.

In SQL Server, the data types for approximate decimal data are Float and Real. For example, the score of 1/3 is recorded as. 3333333, which can be accurately represented when using approximate data types. Therefore, the data retrieved from the system may not be exactly the same as the data stored in the column.

(6) the monetary data indicates the positive or negative amount of money. In Microsoft SQL Server, the data types of currency data are Money and Smallmoney. The Money data type requires 8 storage bytes and the Smallmoney data type requires 4 storage bytes.

(7) Special data types

Special data types include data types that have not been mentioned earlier. There are three special data types, namely Timestamp, Bit, and Uniqueidentifier.

Timestamp is used to represent the sequence of SQL Server activities, expressed in a binary projection format. Timestamp data has nothing to do with inserting data or date and time.

Bit consists of 1 or 0. Use the Bit data type when representing true or false, ON, or OFF. For example, ask if a client request for each visit can be stored in a column of this data type.

Uniqueidentifier consists of 16 bytes of hexadecimal digits that represent a globally unique. GUID is very useful when the record rows of a table are required to be unique. For example, using this data type in the customer identification number column can distinguish different customers.

Thank you for your reading, the above is the content of "what are the data types of Mysql". After the study of this article, I believe you have a deeper understanding of what the data types of Mysql have, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.