MYSQL VARCHAR
MYSQL
The MySQL VARCHAR data type is used to store variable-length string values.
MySQL VARCHAR
➔ VARCHAR is recommended for storing string data where the length is not consistent.
➔ Unlike the MySQL CHAR fixed-length data type, VARCHAR uses only the storage space required for the actual data.
➔ The theoretical maximum length for the VARCHAR data type is 65,535 bytes per column, but this is limited by the maximum row size of 65,535 bytes and the character set used in the table.
➔ Each value stored in the database has an additional length prefix of 1 byte or 2 bytes that records the actual data length.
➔ If the string requires up to 255 bytes, the additional prefix length should be 1 byte.
➔ If the string requires more than 255 bytes, the additional prefix length should be 2 bytes.
VARCHAR(size) Range:(for 1 column)
| Type | VARCHAR (If string requires up to 255 bytes) | VARCHAR (If string requires more than 255 bytes) |
|---|---|---|
| Range | 0 to 65534 | 0 to 65533 |
VARCHAR(size) Range:(for 2 column)
| Type | VARCHAR (If string requires up to 255 bytes) | VARCHAR (If string requires more than 255 bytes) |
|---|---|---|
| Range | 0 to 32766(column1)+1 +32767(column2)+1=total 65535bytes | 0 to 32765(column1)+2 +32766(column2)+2=total 65535bytes |
Difference between CHAR and VARCHAR
➔ This illustrates the difference between CHAR and VARCHAR data type storage in the table below.
➔ Assuming there is only one column of varchar data type and the column uses a single-byte character set such as latin1.
| Value | CHAR(4) | Storage Required | VARCHAR(4) | Storage Required | VARCHAR(255) | Storage Required | VARCHAR(256) | Storage Required |
|---|---|---|---|---|---|---|---|---|
| '' | ' ' | 4 bytes | '' | 1 bytes | '' | 1 bytes | '' | 1 bytes |
| 'ab' | 'ab ' | 4 bytes | 'ab' | 3 bytes | 'ab' | 3 bytes | 'ab' | 3 bytes |
| 'abc' | 'abc ' | 4 bytes | 'abc' | 4 bytes | 'abc' | 4 bytes | 'abc' | 4 bytes |
| 'abcd' | 'abcd' | 4 bytes | 'abcd' | 5 bytes | 'abcd' | 5 bytes | 'abcd' | 5 bytes |
| 'abcdefg......characters<=255' | 'abcd' | 4 bytes | 'abcd' | 5 bytes | 'N characters <= 255' | N + 1 bytes | VARCHAR(N) where, 'N characters > 255' | N + 2 bytes |
When to use the VARCHAR datatype
➔ Any variable length data such as address, first name, last name, email, mobile number, or short to medium length details such as product description, user feedback, user comments, etc.
➔ For larger string storage it is better to use alternative data types like TEXT data type.
Create table:
Example
/* Drop table if the table name is already exists */
DROP TABLE IF EXISTS mytable;
/* Create Table*/
CREATE TABLE mytable(
ID int PRIMARY KEY AUTO_INCREMENT,
NAME varchar(15)
);
/* Insert values*/
INSERT INTO mytable (NAME) VALUES ('Maria Petrov');
INSERT INTO mytable (NAME) VALUES ('Amelia Novak');
/* Query table*/
SELECT * from mytable;
Copy the code and try it out practically in your learning environment.
Insert value:
Example
/* VARCHAR(15) decimal range 0 to 15 chars */
/* Insert values*/
INSERT INTO mytable (NAME, SECTION) VALUES ('Lucas Martin');
/* invalid value, VARCHAR(15) can not hold more than 15 chars */
INSERT INTO mytable (NAME, SECTION) VALUES ('Alexander Müller');
Copy the code and try it out in your learning environment.
Check VARCHAR String value length:
Example
/* Check the length of the values in NAME column */
SELECT ID, NAME, length(NAME) FROM mytable;
Copy the code and try it out practically in your learning environment.
Describe table structure:
Example
/* Check table structure */
DESCRIBE mytable;
Copy the code and try it out practically in your learning environment.
mytable table structure:
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| ID | int(11) | NO | PRI | auto_increment | |
| NAME | varchar(15) | YES |
Query value:
Example
/* Query all data */
SELECT * FROM mytable;
Copy the code and try it out practically in your learning environment.