Mysql data format _ MySQL

Mysql data format: bitsCN.com

Varchar occupies 2 bytes for each English (ASCII) character, and only two bytes for one Chinese character
Char occupies 1 byte for English (ASCII) characters, and the type of Varchar that occupies 2 bytes for a Chinese character is not filled with spaces. for example, varchar (100 ), but its value is only "qian", its value is "qian", and char is different, such as char (100), its value is "qian ", in fact, it is "qian" in the database (there are 96 spaces after qian, that is, it is filled with 100 bytes ).

Char is of a fixed length, so it is much faster than varchar! However, it is a little troublesome for the program to process it. we need to use functions such as trim to remove spaces on both sides!

Ntext

The maximum length of Unicode data with a variable length is 230-1 (1,073,741,823) characters. The storage size is twice the number of input characters (in bytes ). Ntext is synonymous with national text in the SQL-92.

Text

The variable-length non-Unicode data in the server code page can contain a maximum of 231-1 (2,147,483,647) characters. When the server code page uses double-byte characters, the storage volume is still 2,147,483,647 bytes. The storage size may be less than 2,147,483,647 bytes (depending on the string ).

Bigint: integer data from-2 ^ 63 (-9223372036854775808) to 2 ^ 63-1 (9223372036854775807). the storage size is 8 bytes.

Int: integer data from-2 ^ 31 (-2,147,483,648) to 2 ^ 31-1 (2,147,483,647). the storage size is 4 bytes.

Smallint: integer data from-2 ^ 15 (-32,768) to 2 ^ 15-1 (32,767). the storage size is 2 bytes.

Tinyint: integer data from 0 to 255. the storage size is 1 byte.

Bit: an integer of 1 or 0. the storage size is 1 byte.

Unicode data
In Microsoft®SQL Server™In 2000, traditionally non-Unicode data types allow the use of characters defined by specific character sets. The character set is selected when SQL Server is installed and cannot be changed. The Unicode data type allows a column to store any character defined by the Unicode standard, including all characters defined by different character sets. Unicode data types must be twice the storage space of non-Unicode data types.

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

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

1. data type
The data Class is a type of data attribute, indicating the type of information represented by the data. Any computer language defines its own data type. Of course, different programming languages have different characteristics. 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 fixed-length (Binary) or variable-length.
Binary [(N)] is a fixed n-bit Binary data. The value range of n is from 1 to 8000. The memory size is n + 4 bytes.
Varbinary [(N)] is a binary data with n-bit varying length. The value range of n is from 1 to 8000. The memory size is n + 4 bytes, not n bytes.
The data stored in the Image data type is stored as a bit string, which is not explained by SQL Server and must be explained by the application. For example, an application can store data in the Image data type in BMP, TIEF, GIF, and JPEG formats.
(2) character data type
The types of character data include Char, Varchar, and Text.
Character data is composed of any combination of letters, symbols, and numbers.
Varchar is a variable-length data with a length of no more than 8 kB. Char is a fixed-length character data with a maximum length of 8 kB. ASCII data larger than 8 kB can be stored as Text data. For example, because all Html documents are ASCII characters and generally exceed 8 kb in length, these documents can be stored in SQL Server as Text data.
(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 specific character sets. You can select a character set during SQL Server installation. The Unicode data type allows columns to store any characters defined by the Unicode standard. The Unicode standard contains all characters defined in various character sets. When Unicode data types are used, the odds are two times the bytes used by non-Unicode data types.
In SQL Server, Unicode data is stored as Nchar, Nvarchar, and Ntext data. Columns stored with this character type can store characters in multiple character sets. When the length of a column changes, the Nvarchar character type should be used. at this time, a maximum of 4000 characters can be stored. When the column length remains unchanged, the Nchar character type should be used. Similarly, a maximum of 4000 characters can be stored. When the Ntext data type is used, this 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 types are composed of valid date and time. For example, valid date and time data include "4/01/98 12: 15: 00: 00: 00 PM" and "1: 28: 29: 15: 01 AM 8/17/98 ". The previous data type is before the date, the time is before the last data type is before the time, and the date is after. In Microsoft SQL Server, the date and time data types include Datetime and Smalldatetime. the stored date range starts from January 1, January 1, 1753, end on April 9, December 31, 9999 (each value requires 8 storage bytes ). When the Smalldatetime data type is used, the stored date range is from January 1, January 1, 1900 to January 1, December 31, 2079 (each value requires four storage bytes ).
The date format can be set. The command to set the date format is as follows:
Set DateFormat {format | @ format _ var |
Where, format | @ format_var is the date order. Valid parameters include MDY, DMY, YMD, YDM, MYD, and DYM. By default, the date format is MDY.
For example, after you run Set DateFormat YMD, the date format is in the format of year, month, and day. after you run Set DateFormat DMY, the date format is in the format of day, month, and year.
(5) digital data types
Numeric data only contains numbers. Numeric Data types include positive and negative numbers, decimals (floating point numbers), and integers.
Integers are composed of positive integers and negative integers, such as 39, 25, 0-2, and 33967. In Micrsoft SQL Server, integer data types are Int, Smallint, and Tinyint. The range of Int data type stored data is greater than that of Smallint data type stored data, while that of Smallint data type stored data is greater than that of Tinyint data type stored data. The range of data stored by dogs using Int data is from-2 147 483 648 147 to 2 483 647 (each value requires 4 bytes of storage space ). When the Smallint data type is used, the data storage range is from-32 768 to 32 767 (each value requires 2 bytes of storage space ). When the Tinyint data type is used, the data storage range is from 0 to 255 (each value requires 1 byte storage space ).
The exact data types in SQL Server are Decimal and Numeric. The storage space occupied by such data is determined based on the number of digits after the number of digits of the data.
In SQL Server, the data types of approximate decimal data are Float and Real. For example, the 1/3 score is recorded. 3333333. it can be accurately expressed when an approximate data type is used. Therefore, the data retrieved from the system may be different from the data stored in this column.
(6) currency data indicates the number of positive or negative currencies. 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 not mentioned previously. There are three special data types: Timestamp, Bit, and Uniqueidentifier.
Timestamp is used to indicate the sequence of SQL Server activities, in the binary projection format. The Timestamp data has no relationship with the inserted data or the date and time.
Bit is composed of 1 or 0. The Bit data type is used when it indicates true or false, ON, or OFF. For example, you can query whether a client request for each access can be stored in a column of this data type.
Uniqueidentifier consists of 16-byte hexadecimal numbers, indicating a globally unique one. GUID is useful when the record rows in a table must be unique. For example, you can use this data type in the customer ID column to distinguish different customers.

BitsCN.com