So I want to create a command like first_name VARCHAR(30) NOT NULL, ... however I want the database to hold more characters like a text storage type. How would I set up the command for the text type?
1 Answer
Just do
CREATE TABLE foo (
first_name text
);
The ENGINE is InnoDB by default. Your default-character-set should be set to utf8mb4 changing that to the older utf8 is a very bad. Just leave well enough alone.
BTW, I fully agree with Rick James, you really don't want text here. The MySQL best practices would be something like varchar(255) which stores the column in-line. There are storage implications of using text on MySQL (though not in PostgreSQL). From the docs,
Each
BLOBorTEXTvalue is represented internally by a separately allocated object. This is in contrast to all other data types, for which storage is allocated once per column when the table is opened.
They're also a byte bigger than varchar(255)
For more information see
answered Jun 22, 2018 at 20:07
-
1If
first_nameis what it says,TEXTis overkill. How aboutVARCHAR(225)? Guiness says that "Barnaby Marmaduke Aloysius Benjy Cobweb Dartagnan Egbert Felix Gaspar Humbert Ignatius Jayden Kasper Leroy Maximilian Neddy Obiajulu Pepin Quilliam Rosencrantz Sexton Teddy Upwood Vivatma Wayland Xylon Yardley Zachary Usansky" is the longest first_name. Commented Jun 22, 2018 at 20:13 -
@RickJames updated.. But maybe he needs room for future absurdity? =) Commented Jun 22, 2018 at 20:26
-
BLOBandTEXTare not always stored separately; it depends on row format and data length. Still, they have some optimization deficiencies, plus a prohibition of using in anINDEX. (I don't count index prefixing as being useful.) Commented Jun 22, 2018 at 20:30 -
1
When I do use TEXT (x)what do you mean, and what is(x)? Just useTEXTCommented Jun 28, 2018 at 17:54 -
1@wonderhead -
TEXT, by definition can hold up to 64K bytes. There is no need for(x).MEDIUMTEXTcan hold up to 16MB. Commented Jun 28, 2018 at 18:03
CREATE TABLE test (first_name text NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;