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
    Use text instead of varchar?
    – Dave
    Jun 22 '18 at 19:24
  • 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
  • 1
    CREATE TABLE test (first_name text NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    – Dave
    Jun 22 '18 at 19:29
  • @Dave why posting a comment instead of an ANSWER? Jun 22 '18 at 19:54
  • Why would it be different from any other type? The ENGINE=InnoDB is the default, you should also not be using utf8, but utf8mb4 Jun 22 '18 at 20:03

Just do

  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

  • 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? =) 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
  • 1
    When I do use TEXT (x) what do you mean, and what is (x)? Just use TEXT Jun 28 '18 at 17:54
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.