MySQL Tutorials - Herong's Tutorial Examples - v4.46, by Herong Yang
Table Column Types for Character Strings
Describes 4 types of table columns to store character strings: CHAR, VARCHAR, NCHAR and NCHAR VARYING.
There are 4 types of table columns to store character strings: CHAR, VARCHAR, NCHAR and NCHAR VARYING.
"CHARACTER(length) or CHAR(length)" - Character string with fixed length.
"CHARACTER VARYING(length) or VARCHAR(length)" - Character string with varying length.
"NATIONAL CHARACTER(length) or NCHAR(length)" - National character string with fixed length.
"NATIONAL CHARACTER VARYING(length) or NCHAR VARYING(length)" - National character string with varying length.
Examples of character string column types, CharStringColumns.sql:
-- CharStringColumns.sql -- Copyright (c) 1999 HerongYang.com. All Rights Reserved. -- CREATE TABLE CharString ( Line VARCHAR(2), A CHAR(8), B VARCHAR(8), C NCHAR(8), D NCHAR VARYING(8) ); INSERT INTO CharString VALUES ( '1', 'AAA', 'BBB', 'CCC', 'DDD' ); INSERT INTO CharString VALUES ( '2', 'AAAAAA', 'BBBBBB', 'CCCCCC', 'DDDDDD' ); INSERT INTO CharString VALUES ( '3', 'AAAAAAAAA', 'BBBBBBBBB', 'CCCCCCCCC', 'DDDDDDDDD' ); SELECT * FROM CharString; DROP TABLE CharString;
Running CharStringColumns.sql on MySQL 8.0, 5.7 servers, you will get an error because the MySQL server is rejecting character string literals that are longer than their column sizes.
herong> %mysql%\bin\mysql --user=root --password=TopSecret test \ < CharStringColumns.sql ERROR 1406 (22001) at line 25: Data too long for column 'A' at row 1
But running CharStringColumns.sql on MySQL 5.6, 5.0, and 4.0 servers, you will get no errors. But character string data is truncated when the column size is reached.
Line A B C D 1 AAA BBB CCC DDD 2 AAAAAA BBBBBB CCCCCC DDDDDD 3 AAAAAAAA BBBBBBBB CCCCCCCC DDDDDDDD
Table of Contents
MySQL Introduction and Installation
Introduction of MySQL Programs
Perl Programs and MySQL Servers
Java Programs and MySQL Servers
Character Strings and Bit Strings
►Table Column Types for Different Types of Values
►Table Column Types for Character Strings
Table Column Types for Byte Strings
Table Column Types for Bit Strings
Table Column Types for Exact Numbers
Table Column Types for Approximate Numbers
Table Column Types for Date and Time Values
Table Column Types for LOB (Large OBject)
Table Column Types for Look Up Values
Table Column Types for JSON Documents
Table Column Types for Spatial Geometry Data
Using DDL to Create Tables and Indexes
Using DML to Insert, Update and Delete Records
Using SELECT to Query Database
Window Functions for Statistical Analysis
Use Index for Better Performance
Transaction Management and Isolation Levels
Defining and Calling Stored Procedures
Variables, Loops and Cursors Used in Stored Procedures
System, User-Defined and Stored Procedure Variables
Storage Engines in MySQL Server
InnoDB Storage Engine - Primary and Secondary Indexes
Performance Tuning and Optimization
Installing MySQL Server on Linux