MariaDB varchar

This MariaDB tutorial explains, MariaDB varchar and covers the following topics.

• MariaDB varchar
• MariaDB varchar max length
• MariaDB varchar without length
• MariaDB varchar vs text vs char
• MariaDB varchar max size
• MariaDB varchar vs nvarchar
• MariaDB varchar index
• MariaDB varchar not null

MariaDB varchar

The VARCHAR is a data type that represents the character or string when the column in the table is defined as varchar, this means the column will store the strings or characters as values.

The syntax for VARCHAR is given below.

VARCHAR(M) (CHARACTER SET charset_name) (COLLATE collation_name)

Here in the syntax, VARCHAR(M) where M is the length in characters for the columns. The range of M is from 0 to 65,532. The maximum length of VARCHAR depends upon the character set and maximum row size used.

Let’s define some variables with VARCHAR data type.

a VARCHAR(3); - a can store maximum 3 characters

b VARCHAR(10); - b can store maximum 10 characters

In the above code, we have defined the two variables a, b of type varchar(3) and varchar(10).

Read MariaDB LIKE Operator

MariaDB varchar max length

We already know that VARCHAR maximum length is from 0 to 65532, but the length depends upon the character set used. If we use the utf8 character set then the VARCHAR value can be set to a maximum of 21844 characters in length.

Run the below query to show the available character set in the MariaDB.

SHOW CHARACTER SET; -- Command to show the character set

Look at the above output, the column Maxlen shows the maximum length in byte each character set supports per character.

Let’s create a table with columns of the different character sets.

CREATE TABLE character_country(id INT , country_name VARCHAR(20) CHARACTER SET 'big5');

Here in the above code, we have defined the new table as character_country with columns id and country of type INT and VARCHAR respectively.

Also defined the new CHARACTER SET for the column country_name as ‘big5’ and maximum length is 20. So that each character occupies a maximum of two-byte in the memory till 32766 characters.

INSERT INTO character_country(id,country_name)VALUES(1,'United Kindom'),(2,'United State'),(3,'Canada');

SELECT * FROM character_country;

Insert the above records that contain the name of countries like United Kindom, United State, and Canada.

Read MariaDB query examples

MariaDB varchar without length

Without specifying the length of the VARCHAR, it will generate an error. So we can’t define any column of type varchar without the length.

Let’s see with a new table as demo_length.

CREATE TABLE demo_length(id INT , name VARCHAR);

Here we have defined the new table as demo_lenght of columns id, name of type INT, VARCHAR. The data type of column name is VARCHAR without any length.

As we run the above code get the error that is given below in the output.

From the above output, we have concluded that VARCHAR shows the syntax error without length. Let’s fix the above code by providing a certain length to VARCHAR.

CREATE TABLE demo_length(id INT , name VARCHAR(3));

Note: we can provide the zero-length to VARCHAR column data type as VARCHAR(0), this can store two values the NULL or empty string.

Read How to create a database from an SQL file in MariaDB

MariaDB varchar vs text

The following is the difference between VARCHAR and TEXT:

VARCHAR	TEXT	CHAR
VARCHAR column has a maximum length of 65532.	The TEXT column has a maximum length of 65535.	The CHAR stores the fixed length of the string.
It can be fully indexed.	It can be indexed with a specified length.	CHAR is always right-padded spaces to the given length.
The maximum length of VARCHAR depends upon the character set used and the maximum row size.	If the value has multi-byte characters then its maximum length decreases.	The length (M) of CHAR is from 0 to 255 characters.
VARCHAR column can’t be created without specifying the length.	TEXT column can be created without specifying the length	If the length (M) is not specified, then by default is 1.
The syntax is VARCHAR(M) (CHARACTER SET charset_name) (COLLATE collation_name)	The syntax is TEXT(M) (CHARACTER SET charset_name) (COLLATE collation_name)	The syntax is CHAR(M) (CHARACTER SET charset_name) (COLLATE collation_name)

Read MariaDB Insert Into

MariaDB varchar max size

The maximum size of VARCHAR is 255 characters or it stores a maximum of 255 characters.

MariaDB varchar vs nvarchar

In MariaDB, VARCHAR, NVARACHAR AND CHAR is equivalent. All the given below is equivalent to VARCHAR:

VARCHAR(20) CHARACTER SET utf8
NCHAR VARCHAR(20)
NATIONAL CHARACTER VARYING(20)
NATIONAL CHAR VARYING(20)
NATIONAL VARCHAR(20)
NVARCHAR(20)

Read MariaDB create procedure

MariaDB varchar index

In MariaDB, The VARCHAR column can be defined as an index to retrieve the data from the database very quickly. Here we will use the primary key index on the VARCHAR column, the PRIMARY KEY make sure that column must have a unique and not null value.

Let’s create a new table as var_index with a column of type varchar and index.

CREATE TABLE var_index(id INT,city_name VARCHAR(20) PRIMARY KEY);

The above contains the CREATE STATEMENT to create a new table as var_index with columns id and city_name of type INT and VARCHAR. Here we define the column city_name as PRIMARY KEY.

Insert the following records.

INSERT INTO var_index(id,city_name)VALUES(1,'New York'),(2,'Los Angles'),(3,'Chicago');

SELECT * FROM var_index;

Using the above code, we are inserting the three values like New York, Los Angles, Chicago. These values are the city of the United States of America.

Check the created index for the var_index table using the below query.

SHOW INDEXES FROM var_index;

Read How to Change Column in MariaDB

MariaDB varchar not null

The NOT NULL constant can be applied to a column of VARCHAR type in the MariaDB, to make sure that whatever the value stored in the column is not null.

Let’s create a table as var_table as var_table.

CREATE TABLE var_table(id INT ,name VARCHAR(20) NOT NULL);

In the above code, we have defined the new table as var_table with columns id, name of type INT, VARCHAR(20) respectively. Also applied the constraint NOT NULL on the column name.

Now insert the following records only in column id using the below query.

INSERT INTO var_table(id)VALUES(1);

Here in the code, we are inserting the value only in the column id and that value is 1.

After running the above code, we get the error Field name doesn't have default value.

The reason behind getting the above error is that we haven’t provided any value to a name column, and that column is defined as NOT NULL means the column can’t have the null value. So it is necessary to provide some value while inserting the new records or data in the table.

To resolve the above error, we need to insert the value for a column name. Let’s do that using the value query.

INSERT INTO var_table(id,name)VALUES(1,'Elon');

SELECT * FROM var_table;

Now after providing the new value to a column name, the query runs successfully.

As from the output, the name the column has value as ‘Elon’

You may like the following MariaDB tutorials:

• Replace Function in MariaDB
• MariaDB import CSV
• How to Grant User Access to a MariaDB Database
• How to create a user in MariaDB
• MariaDB Timestamp
• MariaDB Delete Row
• MariaDB Date

We discussed here MariaDB varchar with a few examples.

• MariaDB varchar
• MariaDB varchar max length
• MariaDB varchar without length
• MariaDB varchar vs text vs char
• MariaDB varchar max size
• MariaDB varchar vs nvarchar
• MariaDB varchar index
• MariaDB varchar not null