[mysql] Equivalent of varchar(max) in MySQL?

What is the equivalent of varchar(max) in MySQL?

The max length of a varchar in MySQL 5.6.12 is 4294967295.

The max length of a varchar is

65535

divided by the max byte length of a character in the character set the column is set to (e.g. utf8=3 bytes, ucs2=2, latin1=1).

minus 2 bytes to store the length

minus the length of all the other columns

minus 1 byte for every 8 columns that are nullable. If your column is null/not null this gets stored as one bit in a byte/bytes called the null mask, 1 bit per column that is nullable.

TLDR; MySql does not have an equivalent concept of varchar(max), this is a MS SQL Server feature.

What is VARCHAR(max)?

varchar(max) is a feature of Microsoft SQL Server.

The amount of data that a column could store in Microsoft SQL server versions prior to version 2005 was limited to 8KB. In order to store more than 8KB you would have to use TEXT, NTEXT, or BLOB columns types, these column types stored their data as a collection of 8K pages separate from the table data pages; they supported storing up to 2GB per row.

The big caveat to these column types was that they usually required special functions and statements to access and modify the data (e.g. READTEXT, WRITETEXT, and UPDATETEXT)

In SQL Server 2005, varchar(max) was introduced to unify the data and queries used to retrieve and modify data in large columns. The data for varchar(max) columns is stored inline with the table data pages.

As the data in the MAX column fills an 8KB data page an overflow page is allocated and the previous page points to it forming a linked list. Unlike TEXT, NTEXT, and BLOB the varchar(max) column type supports all the same query semantics as other column types.

So varchar(MAX) really means varchar(AS_MUCH_AS_I_WANT_TO_STUFF_IN_HERE_JUST_KEEP_GROWING) and not varchar(MAX_SIZE_OF_A_COLUMN).

MySql does not have an equivalent idiom.

In order to get the same amount of storage as a varchar(max) in MySql you would still need to resort to a BLOB column type. This article discusses a very effective method of storing large amounts of data in MySql efficiently.

The max length of a varchar is

65535

divided by the max byte length of a character in the character set the column is set to (e.g. utf8=3 bytes, ucs2=2, latin1=1).

minus 2 bytes to store the length

minus the length of all the other columns

minus 1 byte for every 8 columns that are nullable. If your column is null/not null this gets stored as one bit in a byte/bytes called the null mask, 1 bit per column that is nullable.

For Sql Server

alter table prg_ar_report_colors add Text_Color_Code VARCHAR(max);

For MySql

alter table prg_ar_report_colors add Text_Color_Code longtext;

For Oracle

alter table prg_ar_report_colors add Text_Color_Code CLOB;

The max length of a varchar in MySQL 5.6.12 is 4294967295.

The max length of a varchar is

65535

divided by the max byte length of a character in the character set the column is set to (e.g. utf8=3 bytes, ucs2=2, latin1=1).

minus 2 bytes to store the length

minus the length of all the other columns

minus 1 byte for every 8 columns that are nullable. If your column is null/not null this gets stored as one bit in a byte/bytes called the null mask, 1 bit per column that is nullable.

Mysql Converting column from VARCHAR to TEXT when under limit size!!!

mysql> CREATE TABLE varchars1(ch3 varchar(6),ch1 varchar(3),ch varchar(4000000))
;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+-------+------+---------------------------------------------+
| Level | Code | Message                                     |
+-------+------+---------------------------------------------+
| Note  | 1246 | Converting column 'ch' from VARCHAR to TEXT |
+-------+------+---------------------------------------------+
1 row in set (0.00 sec)

mysql>

For Sql Server

alter table prg_ar_report_colors add Text_Color_Code VARCHAR(max);

For MySql

alter table prg_ar_report_colors add Text_Color_Code longtext;

For Oracle

alter table prg_ar_report_colors add Text_Color_Code CLOB;

TLDR; MySql does not have an equivalent concept of varchar(max), this is a MS SQL Server feature.

What is VARCHAR(max)?

varchar(max) is a feature of Microsoft SQL Server.

The amount of data that a column could store in Microsoft SQL server versions prior to version 2005 was limited to 8KB. In order to store more than 8KB you would have to use TEXT, NTEXT, or BLOB columns types, these column types stored their data as a collection of 8K pages separate from the table data pages; they supported storing up to 2GB per row.

The big caveat to these column types was that they usually required special functions and statements to access and modify the data (e.g. READTEXT, WRITETEXT, and UPDATETEXT)

In SQL Server 2005, varchar(max) was introduced to unify the data and queries used to retrieve and modify data in large columns. The data for varchar(max) columns is stored inline with the table data pages.

As the data in the MAX column fills an 8KB data page an overflow page is allocated and the previous page points to it forming a linked list. Unlike TEXT, NTEXT, and BLOB the varchar(max) column type supports all the same query semantics as other column types.

So varchar(MAX) really means varchar(AS_MUCH_AS_I_WANT_TO_STUFF_IN_HERE_JUST_KEEP_GROWING) and not varchar(MAX_SIZE_OF_A_COLUMN).

MySql does not have an equivalent idiom.

In order to get the same amount of storage as a varchar(max) in MySql you would still need to resort to a BLOB column type. This article discusses a very effective method of storing large amounts of data in MySql efficiently.