Text storage for mysql

Question

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?

would it be first_name TEXT NOT NULL, ? When I enter that when creating a table, a syntax error comes up. My question was quite vague but I'm just asking for the format for a text command. — wonderhead, Jun 22 '18 at 19:27
CREATE TABLE test (first_name text NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8; — Dave, Jun 22 '18 at 19:29
Why would it be different from any other type? The ENGINE=InnoDB is the default, you should also not be using utf8, but utf8mb4 — Evan Carroll, Jun 22 '18 at 20:03

Evan Carroll · Answer 1 · 2018-06-22T20:51:58.657

0

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 BLOB or TEXT value 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

What is the difference between MySQL VARCHAR and TEXT data types?

edited Jun 22 '18 at 20:51

answered Jun 22 '18 at 20:07

Evan Carroll

63,051
46
242
479

1

If first_name is what it says, TEXT is overkill. How about VARCHAR(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. – Rick James Jun 22 '18 at 20:13
@RickJames updated.. But maybe he needs room for future absurdity? =) – Evan Carroll Jun 22 '18 at 20:26
BLOB and TEXT are not always stored separately; it depends on row format and data length. Still, they have some optimization deficiencies, plus a prohibition of using in an INDEX. (I don't count index prefixing as being useful.) – Rick James Jun 22 '18 at 20:30
@RickJames that's only true for MyISAM. InnoDB always stores blobs and text out-of-line, afaik. – Evan Carroll Jun 22 '18 at 20:50
@RickJames could be worng though seems to be some disagreement over it, have at it https://dba.stackexchange.com/q/210408/2639 – Evan Carroll Jun 22 '18 at 20:55
@RickJames sorry for the late response but the first_name VARCHAR command was fine. For my project I am creating a form that users can write in, which would require more than 255 characters, which is why I want to use TEXT. When I do use TEXT (x) tho it returns a syntax error. – wonderhead Jun 28 '18 at 17:52
1

When I do use TEXT (x) what do you mean, and what is (x)? Just use TEXT – Evan Carroll Jun 28 '18 at 17:54
x is the number of bits so in my case I want to be able to store 65,535 bits – wonderhead Jun 28 '18 at 17:58
and I havent tried just using TEXT so I will try it out rn – wonderhead Jun 28 '18 at 17:58
1

@wonderhead - TEXT, by definition can hold up to 64K bytes. There is no need for (x). MEDIUMTEXT can hold up to 16MB. – Rick James Jun 28 '18 at 18:03

Text storage for mysql

1 Answers1