MySQL and MSSQL, text, ntext, image, blob comparison

Source: Internet
Author: User
Keywords Byte comparison string variable size
Tags binary code page data data type default error image mysql

1, MySQL exists text and blob:

(1), the same
In the storage or retrieval of a text or BLOB column, there is no case conversion, and when it is not running in strict mode, if you assign a BLOB or text column A value that exceeds the maximum length of the column type, the value is truncated to ensure fit. If the truncated character is not a space, a warning is generated. Using strict SQL mode produces an error, and the value is rejected rather than intercepted and given a warning.
Blob and text columns cannot have default values.
Trailing spaces are not deleted when the values of BLOB and text columns are saved or retrieved. (This is the same as varbinary and varchar columns).
For the index of BLOB and text columns, you must specify the length of the index prefix. For char and varchar, the prefix length is optional.

(2), dissimilarity

Text:

The text value is case insensitive
Text is treated as a non-binary string
The text column has a character set, and the values are sorted and compared based on the collation rules of the character set
You can treat the text column as a varchar column
MySQL connector/ODBC defines the text value as LongVarChar
BLOBs can store pictures, text is no good, text can only store plain text files. 4 text types Tinytext, text, Mediumtext, and Longtext correspond to 4 blob types and have the same maximum length and storage requirements.
Blob:

The ordering and comparison of BLOB values are performed in a case-sensitive manner;
A blob is treated as a binary string;
Blob columns do not have character sets, and sort and compare numeric values based on column-valued bytes.

In most respects, you can see a BLOB column as a varbinary column that can be large enough

MySQL connector/ODBC defines BLOB values as LongVarBinary
A blob is a large binary object that can hold a variable number of data. 4 blob types Tinyblob, blobs, Mediumblob, and Longblob differ only in terms of the maximum length at which they can hold the value.

(3) Other:

The Varchar,blob and text types are variable-length types, and their storage requirements depend on the actual length of the column value (expressed in the previous table with L), not on the maximum possible size of the type. For example, a varchar (10) column can hold a string with a maximum length of 10 characters, and the actual storage need is the length of the string, plus 1 bytes to record the length of the string. For the string ' ABCD ', L is 4 and the storage requirement is 5 bytes.
The blob and text types require 1,2,3 or 4 bytes to record the length of the column value, depending on the maximum possible length of the type. varchar need to define size, with a maximum limit of 255; If you assign a value that exceeds the maximum length of a column type to a BLOB or text column, the value is truncated to fit it.

CHAR (n) fixed length, up to 255 characters
VARCHAR (n) variable length, MySQL 4.1 and the previous maximum of 255 characters, MySQL 5 after the largest 65535 bytes
Tinytext variable length, up to 255 characters
TEXT variable length, up to 65,535 characters
Mediumtext variable length, up to 16777215 (2^24-1) characters
Longtext variable length, up to 4294967295 (2^32-1) (4G) characters

2, MSSQL exists text, ntext and image:

ntext:
The maximum length of variable-length Unicode data is 2^30-1 (1,073,741,823) characters. The storage size is twice times 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 2^31-1 (2,147,483,647) characters. When the server code page uses double-byte characters, the storage is still 2,147,483,647 bytes. The storage size may be less than 2,147,483,647 bytes (depending on the string).

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

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: info-contact@alibabacloud.com 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.