MySQL VARCHAR

If your instead to learn about the MySQL union & union all

Varchar in MySQL is a data type used for storing text whose length can have a maximum of 65535 characters. The varchar columns in the table are of variable length string that can hold either numeric or character or both. This data type is capable of storing only 255 characters before version 5.0.3, but with this version and later, it can hold up to 65535 characters. It can be stored in MySQL as a 1-byte or 2-byte length prefix plus actual size. The length prefix specifies the byte length of a string value, not the maximum size that we have set. If values do not require more than 255 bytes, a column uses length prefix plus one byte. If values require more than 255 bytes, a column uses length prefix plus two bytes. The maximum length of a VARCHAR in MySQL is subject to the maximum row size of 65,535 bytes, which is shared among all columns except TEXT/BLOB columns and the character set used. It means the total column should not be more than 65535 bytes.

What does VARCHAR 30 mean?

The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store. For example, CHAR(30) can hold up to 30 characters. The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255.

Which is better VARCHAR or text in MySQL?

In most circumstances, VARCHAR provides better performance, it’s more flexible, and can be fully indexed. If you need to store longer strings, use MEDIUMTEXT or LONGTEXT, but be aware that very large amounts of data can be stored in columns of these types.

Let us understand it with the help of an example.

We are going to create two tables and named it Test1 and Test2. Both tables contain two columns named T1 and T2. Execute the following statement to create a table “Test1“:

CREATE TABLE Test1 (  
    T1 VARCHAR(32765) NOT NULL,  
    T2 VARCHAR(32766) NOT NULL  
)  CHARACTER SET = 'latin1' COLLATE LATIN1_DANISH_CI;  

The above statement created a table successfully because the column length T1 = 32765 plus 2 bytes and T2 = 32766 plus 2 bytes equal to 65535 (32765+2+32766+2). So, the column length satisfies the maximum row size of varchar, which is 65535.

Now, we are going to see what happens if the column size exceeds the maximum size of varchar 65535. Execute the below statement where we have increases the size of column T1 to create a table Test2:

CREATE TABLE Test2 (  
    T1 VARCHAR(32770) NOT NULL,  
    T2 VARCHAR(32766) NOT NULL  
)  CHARACTER SET = 'latin1' COLLATE LATIN1_DANISH_CI;  

After executing the above statement, MySQL produces the error. It means the maximum row size cannot exceed 65,535 bytes. In any case, if it increases, the statement failed, and MySQL will generate an error.

In another case, suppose we have created a table named Test3 using the below statement:

CREATE TABLE Test3 (  
    Id INT PRIMARY KEY AUTO_INCREMENT,  
    Name VARCHAR(5) NOT NULL  
);  

Next, insert values into the table using the below statement:

INSERT INTO Test3(Name) VALUES ('Stephen');  

Now, execute the above statement. MySQL gives the error message: Data is too long for column ‘Name’ at row 1. The output explains it more clearly.

MySQL VARCHAR and Spaces

MySQL does not padded space for varchar values when they are stored. MySQL also retained the trailing spaces when they stored or retrieved varchar values. We can understand it through the following example where an insert statement adds values in the Name column of table Test3:

INSERT INTO Test3(Name) VALUES ('John ');  

Next, execute the SELECT statement to retrieve the values.

SELECT Id, Name, length(Name) FROM Test3;  

It will give the following output where MySQL included the trailing space in the counting of length because it does not increase the column length.

However, when we try to insert a varchar value with trailing spaces which exceeded the column length, the MySQL will truncate the trailing spaces. Also, MySQL issues a warning. The following example explains it more clearly:

INSERT INTO Test3(Name) VALUES ('Peter ');  

The above statement inserts a value whose length is six into the name column. The value still inserted into the column, but MySQL truncates the trailing space before adding the value. We can verify using the below query where we can see the insert statement added successfully but with a warning that gives: Data truncated for column ‘name’ at row 1:

Difference between Char and Varchar Data Type

The char and varchar data types are both follow ASCII character. They are almost the same but differ in storing and retrieving the data from the database. The following table summarises the essential differences between char and varchar data type:

MySQL VARCHAR Maximum Length

Although VARCHAR supports the maximum size at 65535 characters, the actual maximum value depends on other columns in the table and character set:

• Maximum row size is 65535 bytes in MySQL that shared among all columns in the table, except TEXT/BLOB columns
• A character set may require more than 1 byte per character (up to 3 bytes in UTF-8) that further limits the maximum length of VARCHAR

For example, in a UTF-8 database (default in MySQL), you can create VARCHAR column with maximum length at 21,844 characters only:

   -- UTF-8 database
   CREATE TABLE t_varchar1u
   (
      c1 VARCHAR(21844)
   );
   -- Table created.
 
   CREATE TABLE t_varchar1u2
   (
      c1 VARCHAR(21845)
   );
   -- Error Code: 1118. Row size too large. 
   -- The maximum row size for the used table type, not counting BLOBs, is 65535.

If a table contains multiple VARCHAR columns, the maximum size for each column is reduced even more:

   -- UTF-8 database
   CREATE TABLE t_varchar1u2
   (
      c1 VARCHAR(8000),
      c2 VARCHAR(8000),
      c3 VARCHAR(8000)
   );
   -- Error Code: 1118. Row size too large. 
   -- The maximum row size for the used table type, not counting BLOBs, is 65535.

Note. You can use TEXT columns to overcome the MySQL row size limit (see below).

Differences Between MySQL VARCHAR and TEXT Data Types

Both VARCHAR and TEXT can store data up to 65535 bytes, and besides the maximum length restriction on VARCHAR described above, they have other differences:

• VARCHAR can have DEFAULT, TEXT cannot

A VARCHAR column can have a DEFAULT, while a TEXT column cannot:

  CREATE TABLE t_varchar1d
  (
    c1 VARCHAR(10) DEFAULT 'A'
  );
  -- Table created
 
  CREATE TABLE t_text1d
  (
    c1 TEXT DEFAULT 'A'
  );
  -- Error Code: 1101. BLOB/TEXT column 'c1' can't have a default value

• Restricting Column Length

By specifying the column size, VARCHAR(n) prevents from inserting values that exceed n characters. A TEXT column does not have such a restriction.

• No Row Size Limit on TEXT Columns

Row size limit at 65,535 bytes does not apply to TEXT columns, and you can have multiple TEXT columns in a table that store up to 65,535 bytes each.

MySQL VARCHAR in Other Databases

Similar data types in other databases:

Oracle:

SQL Server:

PostgreSQL:

MySQL VARCHAR Data Type Conversion to Other Databases

Convertion of VARCHAR data type:

MySQL:

   CREATE TABLE t_varchar1
   (
      c1 VARCHAR(0),
      c2 VARCHAR(100),
      c3 VARCHAR(15000)
   );

Oracle:

Oracle does not allow VARCHAR2(0), and the maximum length is 4,000 bytes.

Note. Although you can use Oracle CLOB columns in string functions, you cannot use them in GROUP BY and ORDER BY.

  CREATE TABLE t_varchar1
   (
      c1 VARCHAR2(1),
      c2 VARCHAR2(100),
      c3 CLOB
   );

SQL Server:

SQL Server does not allow VARCHAR(0), and the maximum length is 8,000 bytes:

   CREATE TABLE t_varchar1
   (
      c1 VARCHAR(1),
      c2 VARCHAR(100),
      c3 VARCHAR(max)
   );

PostgreSQL:

PostgreSQL does not allow VARCHAR(0), but the maximum length is 1 Gb:

  CREATE TABLE t_varchar1
   (
      c1 VARCHAR(0),
      c2 VARCHAR(100),
      c3 VARCHAR(15000)
   );