- MySQL Basics
- MySQL - Home
- MySQL - Introduction
- MySQL - Features
- MySQL - Versions
- MySQL - Variables
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Node.js Syntax
- MySQL - Java Syntax
- MySQL - Python Syntax
- MySQL - Connection
- MySQL - Workbench
- MySQL Databases
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Show Database
- MySQL - Copy Database
- MySQL - Database Export
- MySQL - Database Import
- MySQL - Database Info
- MySQL Users
- MySQL - Create Users
- MySQL - Drop Users
- MySQL - Show Users
- MySQL - Change Password
- MySQL - Grant Privileges
- MySQL - Show Privileges
- MySQL - Revoke Privileges
- MySQL - Lock User Account
- MySQL - Unlock User Account
- MySQL Tables
- MySQL - Create Tables
- MySQL - Show Tables
- MySQL - Alter Tables
- MySQL - Rename Tables
- MySQL - Clone Tables
- MySQL - Truncate Tables
- MySQL - Temporary Tables
- MySQL - Repair Tables
- MySQL - Describe Tables
- MySQL - Add/Delete Columns
- MySQL - Show Columns
- MySQL - Rename Columns
- MySQL - Table Locking
- MySQL - Drop Tables
- MySQL - Derived Tables
- MySQL Queries
- MySQL - Queries
- MySQL - Constraints
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Replace Query
- MySQL - Insert Ignore
- MySQL - Insert on Duplicate Key Update
- MySQL - Insert Into Select
- MySQL Indexes
- MySQL - Indexes
- MySQL - Create Index
- MySQL - Drop Index
- MySQL - Show Indexes
- MySQL - Unique Index
- MySQL - Clustered Index
- MySQL - Non-Clustered Index
- MySQL Operators and Clauses
- MySQL - Where Clause
- MySQL - Limit Clause
- MySQL - Distinct Clause
- MySQL - Order By Clause
- MySQL - Group By Clause
- MySQL - Having Clause
- MySQL - AND Operator
- MySQL - OR Operator
- MySQL - Like Operator
- MySQL - IN Operator
- MySQL - ANY Operator
- MySQL - EXISTS Operator
- MySQL - NOT Operator
- MySQL - NOT EQUAL Operator
- MySQL - IS NULL Operator
- MySQL - IS NOT NULL Operator
- MySQL - Between Operator
- MySQL - UNION Operator
- MySQL - UNION vs UNION ALL
- MySQL - MINUS Operator
- MySQL - INTERSECT Operator
- MySQL - INTERVAL Operator
- MySQL Joins
- MySQL - Using Joins
- MySQL - Inner Join
- MySQL - Left Join
- MySQL - Right Join
- MySQL - Cross Join
- MySQL - Full Join
- MySQL - Self Join
- MySQL - Delete Join
- MySQL - Update Join
- MySQL - Union vs Join
- MySQL Keys
- MySQL - Unique Key
- MySQL - Primary Key
- MySQL - Foreign Key
- MySQL - Composite Key
- MySQL - Alternate Key
- MySQL Triggers
- MySQL - Triggers
- MySQL - Create Trigger
- MySQL - Show Trigger
- MySQL - Drop Trigger
- MySQL - Before Insert Trigger
- MySQL - After Insert Trigger
- MySQL - Before Update Trigger
- MySQL - After Update Trigger
- MySQL - Before Delete Trigger
- MySQL - After Delete Trigger
- MySQL Data Types
- MySQL - Data Types
- MySQL - VARCHAR
- MySQL - BOOLEAN
- MySQL - ENUM
- MySQL - DECIMAL
- MySQL - INT
- MySQL - FLOAT
- MySQL - BIT
- MySQL - TINYINT
- MySQL - BLOB
- MySQL - SET
- MySQL Regular Expressions
- MySQL - Regular Expressions
- MySQL - RLIKE Operator
- MySQL - NOT LIKE Operator
- MySQL - NOT REGEXP Operator
- MySQL - regexp_instr() Function
- MySQL - regexp_like() Function
- MySQL - regexp_replace() Function
- MySQL - regexp_substr() Function
- MySQL Fulltext Search
- MySQL - Fulltext Search
- MySQL - Natural Language Fulltext Search
- MySQL - Boolean Fulltext Search
- MySQL - Query Expansion Fulltext Search
- MySQL - ngram Fulltext Parser
- MySQL Functions & Operators
- MySQL - Date and Time Functions
- MySQL - Arithmetic Operators
- MySQL - Numeric Functions
- MySQL - String Functions
- MySQL - Aggregate Functions
- MySQL Misc Concepts
- MySQL - NULL Values
- MySQL - Transactions
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - SubQuery
- MySQL - Comments
- MySQL - Check Constraints
- MySQL - Storage Engines
- MySQL - Export Table into CSV File
- MySQL - Import CSV File into Database
- MySQL - UUID
- MySQL - Common Table Expressions
- MySQL - On Delete Cascade
- MySQL - Upsert
- MySQL - Horizontal Partitioning
- MySQL - Vertical Partitioning
- MySQL - Cursor
- MySQL - Stored Functions
- MySQL - Signal
- MySQL - Resignal
- MySQL - Character Set
- MySQL - Collation
- MySQL - Wildcards
- MySQL - Alias
- MySQL - ROLLUP
- MySQL - Today Date
- MySQL - Literals
- MySQL - Stored Procedure
- MySQL - Explain
- MySQL - JSON
- MySQL - Standard Deviation
- MySQL - Find Duplicate Records
- MySQL - Delete Duplicate Records
- MySQL - Select Random Records
- MySQL - Show Processlist
- MySQL - Change Column Type
- MySQL - Reset Auto-Increment
- MySQL - Coalesce() Function
MySQL - VARCHAR
The MySQL Varchar Data Type
The MySQL VARCHAR data type is used to store variable-length character strings, having a length up to 65,535 bytes.
In MySQL, when you store text in a VARCHAR column, it needs a little extra space to keep track of how long the text is. This extra space can be either 1 or 2 bytes, depending on the length of the text. If the text is short (less than 255 characters), it uses 1 byte for length. For longer text, it uses 2 bytes.
The total size of data plus the length info cannot exceed 65,535 bytes for a row in a table.
Example
In the following query, we are creating a new table named test_table that has two columns column1 and column2.
As we can see in the below code block, the columns (column1 = 32765 and column2 = 32766) makes 65531 bytes. These columns will take 2 bytes each as a length prefix. Therefore, the columns totally make 32765+2+32766+2 = 65535 bytes −
CREATE TABLE test_table ( column1 VARCHAR(32765) NOT NULL, column2 VARCHAR(32766) NOT NULL )CHARACTER SET 'latin1' COLLATE LATIN1_DANISH_CI;
Output
Following is the output of the above code −
Query OK, 0 rows affected (0.03 sec)
Example
Now, let us create another table test_table2 and provide 32766 and 32766 to both the columns (column1 and column2) −
CREATE TABLE test_table2 ( column1 VARCHAR(32766) NOT NULL, --error column2 VARCHAR(32766) NOT NULL )CHARACTER SET 'latin1' COLLATE LATIN1_DANISH_CI;
Output
As we can see in the output below, an error is generated because the row size (32766 +2 +32766 +2 = 65536) exceeds the maximum limit (65,535) −
ERROR 1118 (42000): 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
Example
Here, we are creating another table named CUSTOMERS using the following query −
CREATE TABLE CUSTOMERS ( ID int PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(3) );
Following is the output obtained −
Query OK, 0 rows affected (0.03 sec)
Now, we are inserting a string into NAME column where the length is greater than the length of VARCHAR column −
INSERT INTO CUSTOMERS (NAME) VALUES ('Rahul');
Output
As a result, MySQL will generate an error given below −
ERROR 1406 (22001): Data too long for column 'NAME' at row 1
Example
MySQL does not count the trailing spaces when inserting a value. Instead it truncates the trailing spaces.
Let us insert a value into the NAME column that has trailing spaces −
INSERT INTO CUSTOMERS (NAME) VALUES ('ABC ');
Output
As we can see in the output below, MySQL issued a warning −
Query OK, 1 row affected, 1 warning (0.02 sec)
Example
In the following query, we are trying to check the length of the values in NAME column −
SELECT ID, NAME, length(NAME) FROM CUSTOMERS;
The result produced is as follows −
ID | NAME | length(NAME) |
---|---|---|
1 | ABC | 3 |
Now, let us execute the below query to display the warnings that issued on the above insertion operation −
SHOW warnings;
The result produced is −
Level | Code | Message |
---|---|---|
Note | 1265 | Data truncated for column 'NAME' at row 1 |
Varchar Datatypes Using a Client Program
In addition to performing datatypes using mysql query, we can also create column of the Varchar datatypes using the client program.
Syntax
To create a column of Varchar datatypes through a PHP program, we need to execute the "CREATE TABLE" statement using the mysqli function query() as follows −
$sql ="CREATE TABLE customers (cust_Name VARCHAR(30), cust_Address varchar(50)) "; $mysqli->query($sql);
To create a column of Varchar datatypes through a JavaScript program, we need to execute the "CREATE TABLE" statement using the query() function of mysql2 library as follows −
sql = "CREATE TABLE customers (cust_Name VARCHAR(30), cust_Address varchar(50))"; con.query(sql);
To create a column of Varchar datatypes through a Java program, we need to execute the "CREATE TABLE" statement using the JDBC function execute() as follows −
String sql = "CREATE TABLE customers (cust_Name VARCHAR(30), cust_Address varchar(50))"; statement.execute(sql);
To create a column of Varchar datatypes through a python program, we need to execute the "CREATE TABLE" statement using the execute() function of the MySQL Connector/Python as follows −
sql = 'CREATE TABLE test_table (column1 VARCHAR(32765) NOT NULL, column2 VARCHAR(32766) NOT NULL)CHARACTER SET 'latin1' COLLATE LATIN1_DANISH_CI' cursorObj.execute(sql)
Example
Following are the programs −
$dbhost = 'localhost'; $dbuser = 'root'; $dbpass = 'password'; $dbname = 'TUTORIALS'; $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname); if ($mysqli->connect_errno) { printf("Connect failed: %s
", $mysqli->connect_error); exit(); } // printf('Connected successfully.
'); //create a customer table and use varchar data type with differenet size $sql = "CREATE TABLE customers (cust_Name VARCHAR(30), cust_Address varchar(50)) "; if ($mysqli->query($sql)) { echo "Table created successfully with varchar data!\n"; } if ($mysqli->errno) { printf("table could not create table: %s
", $mysqli->error); } $mysqli->close();
Output
The output obtained is as follows −
Table created successfully with varchar data!
var mysql = require("mysql2"); var con = mysql.createConnection({ host: "localhost", user: "root", password: "password", }); //Connecting to MySQL con.connect(function (err) { if (err) throw err; // console.log("Connected successfully...!"); // console.log("--------------------------"); sql = "USE TUTORIALS"; con.query(sql); //create a customer table and use varchar data type with differenet size sql = "CREATE TABLE customers (cust_Name VARCHAR(30), cust_Address varchar(50)) "; con.query(sql); sql = `SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'customers' AND COLUMN_NAME = 'cust_Name'`; con.query(sql, function (err, result) { if (err) throw err; console.log(result); }); });
Output
The output produced is as follows −
[ { DATA_TYPE: 'varchar' } ]
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Varchar { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/TUTORIALS"; String username = "root"; String password = "password"; try { Class.forName("com.mysql.cj.jdbc.Driver"); Connection connection = DriverManager.getConnection(url, username, password); Statement statement = connection.createStatement(); System.out.println("Connected successfully...!"); //varchar data types...!; String sql = "CREATE TABLE customers (cust_Name VARCHAR(30), cust_Address varchar(50))"; statement.execute(sql); System.out.println("column of a varchar type created successfully...!"); ResultSet resultSet = statement.executeQuery("DESCRIBE customers"); while (resultSet.next()){ System.out.println(resultSet.getString(1)+" "+resultSet.getString(2)); } connection.close(); } catch (Exception e) { System.out.println(e); } } }
Output
The output obtained is as shown below −
Connected successfully...! column of a varchar type created successfully...! cust_Name varchar(30) cust_Address varchar(50)
import mysql.connector # Establishing the connection connection = mysql.connector.connect( host='localhost', user='root', password='password', database='tut' ) # Creating a cursor object cursorObj = connection.cursor() # Create table with varchar column sql = ''' CREATE TABLE test_table ( column1 VARCHAR(32765) NOT NULL, column2 VARCHAR(32766) NOT NULL ) CHARACTER SET 'latin1' COLLATE LATIN1_DANISH_CI ''' cursorObj.execute(sql) print("The table is created successfully!") # Insert data into the created table insert_query = "INSERT INTO test_table (column1, column2) VALUES (%s, %s)" data_to_insert = ("History", "Geography") cursorObj.execute(insert_query, data_to_insert) # Commit the changes after the insert operation connection.commit() print("Rows inserted successfully.") # Now display the table records select_query = "SELECT * FROM test_table" cursorObj.execute(select_query) result = cursorObj.fetchall() print("Table Data:") for row in result: print(row) cursorObj.close() connection.close()
Output
Following is the output of the above code −
The table is created successfully! Rows inserted successfully. Table Data: ('History', 'Geography')