MySql TEXT Data Type
🙏 Please consider SUBSCRIBING !!! 🧧 Patreon Donations: / theflask
Click "SHOW MORE" below to see the cool guides !!! 😎
MySql TEXT Data Type
References:
https://dev.mysql.com/doc/refman/5.7/...
https://dev.mysql.com/doc/refman/5.7/...
https://www.mysqltutorial.org/mysql-t...
https://dev.mysql.com/doc/refman/5.7/...
https://dev.mysql.com/doc/refman/5.7/...
https://gist.github.com/joemalski/b75...
TEXT - ideal for storing large string data (character strings)
it can't have DEFAULT value
automatically adjust to it's text family depending on the display size
you specify in TEXT(display_size)
you can directly use TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT
without having to specify the display size.
TEXT Family:
NOTE: This is applicable if your chosen CHARSET takes 1 byte
to store a character, for example, latin1. Sadly, utf8
doesn't which is the default CHARSET since it uses
3 bytes to store a character.
TINYTEXT (255 characters, 255 bytes)
TEXT (65,535 characters, 65 KB)
MEDIUMTEXT (16,777,215 characters, 16 MB)
LONGTEXT (4,294,967,295 characters, 4 GB) using all memory!
Note: If you don't specify the ENGINE, CHARSET or COLLATE when creating
tables, MySql will use the default which is that ENGINE=InnoDB,
CHARSET=utf8 and COLLATE=utf8_general_ci.
Check the CHARACTER SET's or CHARSET's Maxlen
Maxlen is the size it uses to store a character
example latin1 uses 1 byte, utf8 used 3 bytes.
SHOW CHARACTER SET;
-- DEFAULT example
CREATE TABLE test (
id INT DEFAULT 10,
name VARCHAR(50)
);
DESC test;
INSERT INTO test (name) VALUES ('Trump');
SELECT * FROM test;
-- ERROR, DEFAULT will not work on TEXT
DROP TABLE test;
CREATE TABLE test (
country TEXT(50) DEFAULT 'US'
);
-- Creating Columns using TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT
-- Note: CHARSET=utf8 which is the default
CREATE TABLE test (
c1 TINYTEXT,
c2 TEXT,
c3 MEDIUMTEXT,
c4 LONGTEXT
);
-- Creating Columns using TEXT(display_size)
-- Note: CHARSET=utf8 which is the default
CREATE TABLE test (
-- The 2 are equivalent
c1 TEXT,
c2 TEXT(0)
);
-- using latin1, uses 1 byte to save 1 character
DROP TABLE test;
CREATE TABLE test (
c1 TEXT(255), -- if latin1, 1-255 is TINYTEXT
c2 TEXT(65535), -- if latin1, 256-65535 is TEXT
c3 TEXT(16777215), -- if latin1, 65536-16777215 is MEDIUMTEXT
c4 TEXT(4294967295) -- if latin1, 16777216-4294967295 is LONGTEXT
) ENGINE=InnoDB CHARSET=latin1;
-- using utf8, uses 3 bytes to save 1 character
DROP TABLE test;
CREATE TABLE test (
c1 TEXT(85), -- if utf8, 1-85 is TINYTEXT (255 / 3)
c2 TEXT(21845), -- if utf8, 86-21845 is TEXT (65536 / 3)
c3 TEXT(5592405), -- if utf8, 21846-5592405
-- is MEDIUMTEXT (16777215 / 3)
c4 TEXT(4294967295) -- if utf8, 5592405-4294967295 is LONGTEXT
) ENGINE=InnoDB CHARSET=utf8;
🙏 Please consider SUBSCRIBING !!! 🧧 Patreon Donations: / theflask
DISCLAIMER:
All product and company names are trademarks™ or registered® trademarks of their respective holders. Use of them does not imply any affiliation with or endorsement by them.
Use of MySQL Conditional Use Logos:
https://bit.ly/3iwhu4I