Does mysql have varchar2?

Mysql does not have varchar2, only varchar. VARCHAR represents a variable-length string, which can be up to 65,535 characters long; MySQL treats VARCHAR values ​​as a 1-byte or 2-byte length prefix plus the actual data. VARCHAR2 is an Oracle type that represents a variable length string and can store values ​​from 1 to 4000 bytes, which means that for a single-byte character set, up to 4000 characters can be stored in a VARCHAR2 column.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Mysql does not have varchar2, only varchar. Oracle has varchar2, which represents the byte length. How many Chinese characters can be accommodated is related to the character set of the database.

MySQL's varchar type

VARCHAR is a variable-length string whose length can reach 65,535 characters. MySQL treats VARCHAR values ​​as a 1-byte or 2-byte length prefix plus the actual data.

The length prefix specifies the number of bytes of the value. If the column requires less than 255 bytes, the length prefix is ​​1 byte. If the column requires more than 255 bytes, the length prefix is ​​two length bytes.

However, the maximum length is limited by the maximum line size (65,535 bytes) and the character set used. This means that the total length of all columns should be less than 65,535 bytes.

Let’s take a look at an example.

Create a new table with two columns s1 and s2, with lengths of 32765 (length prefix 2) and 32766 (2) respectively. Note that 32765 2 32766 2 = 65535, which is the maximum row size.

USE testdb;

CREATE TABLE IF NOT EXISTS varchar_test (
    s1 VARCHAR(32765) NOT NULL,
    s2 VARCHAR(32766) NOT NULL
)  CHARACTER SET 'latin1' COLLATE LATIN1_DANISH_CI;

This statement successfully created the table. However, if we increase the length of column s1 by 1.

USE testdb;
CREATE TABLE IF NOT EXISTS varchar_test_2 (
    s1 VARCHAR(32766) NOT NULL, -- error
    s2 VARCHAR(32766) NOT NULL
)  CHARACTER SET 'latin1' COLLATE LATIN1_DANISH_CI;

MySQL will issue an error message:

Error Code: 1118. Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs 0.000 sec

As shown above, the row length is too large, so the create statement failed.

MySQL will issue an error if you insert a string whose length is greater than the length of the VARCHAR column. Consider the following example:

USE testdb;

CREATE TABLE items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(3)
);

INSERT INTO items(title)
VALUES('ABCD');

In this example, MySQL issues the following error message:

1406 - Data too long for column 'title' at row 1

varchar2 type from oracle

To store variable-length strings, you can use the Oracle VARCHAR2 data type. VARCHAR2 columns can store values ​​from 1 to 4000 bytes. This means that for a single-byte character set, up to 4000 characters can be stored in a VARCHAR2 column.

When creating a table with a VARCHAR2 column, you must specify the maximum string length (in bytes):

VARCHAR2(max_size BYTE)

or in characters -

VARCHAR2(max_size CHAR)

By default, If no BYTE or CHAR is explicitly specified after max_size, Oracle uses BYTE. In other words, a VARCHAR2(N) column can hold up to N bytes of characters.

Oracle issues an error if you store a string whose size exceeds the maximum length of a VARCHAR2 column.

For example, if you define a VARCHAR2 column with a maximum length of 20, you can store up to 20 characters in a single-byte character set. If 21 or more characters are stored, Oracle returns an error.

Also, if you store 10 characters in a VARCHAR2(20) column, Oracle only uses 10 bytes for storage instead of 20 bytes. Therefore, using VARCHAR2 data type can help you save space used by your table.

When comparing VARCHAR2 values, Oracle uses non-padded comparison semantics.

VARCHAR2 maximum length

Starting from Oracle 12c, the maximum length that can be VARCHAR2 data type is: 32767. Oracle uses the MAX_STRING_SIZE parameter to control the maximum length. If MAX_STRING_SIZE is STANDARD, the maximum size of VARCHAR2 is 4000 bytes. If MAX_STRING_SIZE is EXTENDED, the size limit of VARCHAR2 is 32767.

To get the value of the MAX_STRING_SIZE parameter, please use the following query:

SELECT
    name,
    value
FROM
    v$parameter
WHERE
    name = 'max_string_size';

Execute the above query statement and get the following results-

SHOW PARAMETER max_string_size;

[Related recommendations: mysql video Tutorial】

The above is the detailed content of Does mysql have varchar2?. For more information, please follow other related articles on the PHP Chinese website!