Mysql data format

Varchar occupies 2 bytes for each English (ASCII) character, and only occupies two bytes for each Chinese character.
char occupies 1 byte for English (ASCII) characters, and 2 bytes for each Chinese character. Varchar The type is not filled with spaces, 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", In fact, it is "qian" in the database (there are 96 spaces after qian, which means it is filled to 100 bytes).

Since char is of fixed length, it is much faster than varchar! But the program is a little more troublesome to process. You need to use functions like trim to remove the spaces on both sides!

ntext

Variable length The maximum length of Unicode data is 230 - 1 (1,073,741,823) characters. The storage size is twice the number of characters entered (in bytes). The SQL-92 synonym for ntext is national text.

text

The maximum length of variable-length non-Unicode data in the server code page is 231-1 (2,147,483,647) characters. When the server code page uses double-byte characters, the storage amount is still 2,147,483,647 bytes. 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), storage size is 8 bytes.

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

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

tinyint: Integer data from 0 to 255, storage size is 1 byte.

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

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

Unicode data is stored using the nchar, varchar, and ntext data types in SQL Server. Use these data types for columns that store characters from multiple character sets. Use the nvarchar type when the items in the column contain a different number of Unicode characters (up to 4000). Use the nchar type when the items in the column are of the same fixed length (up to 4000 Unicode characters). When any item in the column exceeds 4000 Unicode characters, use the ntext type.

Explanation The Unicode data type of SQL Server is based on the national 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
Data type is an attribute of data, indicating the type of information represented by the data. Every computer language defines its own data types. 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
·T imestamp
·Uniqueidentifier
·Text
·Image
·Ntext

(1) Binary data type
Binary data includes Binary , Varbinary and Image.
Binary data type can be either fixed length (Binary) or variable length.
Binary[(N)] is n-bit fixed binary data. Among them, the value range of n is from 1 to 8000. Its storage size is n + 4 bytes.
Varbinary[(N)] is n-bit variable-length binary data. Among them, the value range of n is from 1 to 8000. The size of its storage is n + 4 bytes, not n bytes.
Data stored in the Image data type is stored as a bit string and is not interpreted by SQL Server and must be interpreted by the application. For example, applications can store data in the Image data type using BMP, TIEF, GIF, and JPEG formats.
(2)Character data type
Character data types include Char, Varchar and Text.
Character data is data composed of any combination of letters, symbols and numbers.
Varchar is variable-length character data, its length does not exceed 8KB. Char is fixed-length character data with a maximum length of 8KB. ASCII data exceeding 8KB can be stored using the Text data type. For example, because HTML documents are all ASCII characters and are typically more than 8KB in length, these documents can be stored in SQL Server with the Text data type.
(3)Unicode data types
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 Unicode data types, any character defined by the Unicode standard can be stored in a column. In the Unicode standard, all characters defined in various character sets are included. Using Unicode data types, the space occupied is twice the size of the space occupied by using non-Unicode data types.
In SQL Server, Unicode data is stored in Nchar, Nvarchar and Ntext data types. Columns stored using this character type can store characters from multiple character sets. When the column length varies, the Nvarchar character type should be used, 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.
Date and time data types consist of valid dates and times. 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 former data type has the date in front and the time in the back. The latter data type has the instant in front and the date in the back. In Microsoft SQL Server, when the date and time data types include Datetime and Smalldatetime, the stored date range starts from 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 begins on January 1, 1900, and ends on December 31, 2079 (each value requires 4 bytes of storage).
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 order of dates. Valid parameters include MDY, DMY, YMD, YDM, MYD, and DYM. By default, the date format is MDY.
For example, after executing Set DateFormat YMD, the format of the date is in the form of year, month and day; when executing Set DateFormat DMY, the format of the date is in the form of day, month and year
(5) Numeric data type
Numeric data only contains 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 Microsoft SQL Server, the data types for integer storage are Int, Smallint and Tinyint. The Int data type stores data in a larger range than the Smallint data type, and the Smallint data type stores data in a larger range than the Tinyint data type. The range of data stored using Int data is from -2 147 483 648 to 2 147 483 647 (each value requires 4 bytes of storage space). When using the Smallint data type, the range of stored data is from -32 768 to 32 767 (each value requires 2 bytes of storage space). 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 precise Xiaolou data in SQL Server are Decimal and Numeric. The storage space occupied by this data is determined based on the number of bits after the number of bits of the data.
In SQL Server, the data types for approximate decimal data are Float and Real. For example, the fraction one third is written as . 3333333, 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) Currency data represents a positive or negative currency quantity. In Microsoft SQL Server, the data types for monetary data are Money and Smallmoney. The Money data type requires 8 bytes of storage, and the Smallmoney data type requires 4 bytes of storage.
(7) Special data types
Special data types include data types not mentioned before. There are three special data types, namely Timestamp, Bit and Uniqueidentifier.
Timestamp is used to represent the sequence of SQL Server activities, expressed in binary projection format. Timestamp data has nothing to do with inserted data or dates and times.
Bit consists of 1 or 0. The Bit data type is used when representing true or false, ON or OFF. For example, a client request that asks for every access can be stored in a column of this data type.
Uniqueidentifier consists of 16 bytes of hexadecimal numbers, representing a globally unique one. GUIDs are very useful when the rows of a table must be unique. For example, use this data type in a customer identification number column to distinguish between different customers.