MySQL Workbench TEXT() Column Parameter

Question

I dont usually use MySQL workbench, I am trying to create a table with a text column by choosing TEXT(), however I am not sure what value I should put in between the '()'. If I leave it blank it gives me the error:

The given data type TEXT() contains errors and cannot be accepted. The previous value is kept instead.

I can put a number inside the parentheses but I wanted to know how it affects the column.

I could not find any answers online. I know how it affects things such as DATETIME() but not this data type.

Andy G · Answer 1 · 2017-07-24T15:55:09.917

11

In TEXT(M) M is an optional length. From the docs:

An optional length M can be given for this type. If this is done, MySQL creates the column as the smallest TEXT type large enough to hold values M characters long.

So you can use it to specify the maximum number of characters you anticipate for the field.

If you omit the parens - just TEXT - it will default to 65,535. Otherwise, if you supply the parens and a value it will default to the appropriate text type (TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT).

See here for a comparison with VARCHAR. One difference is that you cannot directly index a TEXT field, it requires a prefix.

edited Jul 24 '17 at 15:55

answered Jul 23 '17 at 21:10

Andy G

19,232
5
47
69

Thanks, I guess I was thinking about the data type wrong. I thought if we left it blank it would default to some value. – user126865 Jul 23 '17 at 21:22
I've added a little more info to my answer. – Andy G Jul 23 '17 at 21:36
1

Yes, you can index a text field, just you must use a prefix. – Shadow Jul 23 '17 at 22:52
Thank you for the clarification. I've adjusted my answer. – Andy G Jul 24 '17 at 15:55

MySQL Workbench TEXT() Column Parameter

1 Answers1