What datatype should i choose for mysql db to store a paragraph text which includes special characters as well?

Question

In my MySql DB I want to store some text which will be a mimimun of 1000 words, it will contain some special characters like "",'()<>-.!; data will be passed using php.

Answer 1

There are several datatypes that hold text. When you declare any of them you can also specify their collation and character set. For example:

CREATE OR REPLACE TABLE mytable {
    mytext VARCHAR(2000) COLLATE utf8mb4_general_ci
)

This gives you a place to store up to 2000 characters of Unicode text. Each Unicode character can take from 1 to 4 bytes of storage. Unicode, as you know, handles many different human scripts, not to mention emojis and math symbols.

To store text you have

• the CHAR() data type if you know upfront exactly how many characters you must store.
• the VARCHAR() data type. It has best performance especially if you want to filter on it.
• the so-called LOB (large object) data types:
  • LONGTEXT ... up to 4GiB in your text. This is what WordPress uses for content, for what it's worth.
  • MEDIUMTEXT ... up to 16MiB
  • TEXT ... up to 64KiB
  • TINYTEXT ... up to 255 bytes (useless, use VARCHAR(255) instead)

These all have reasonable overhead in terms of disk space used.

If you want to put indexes on VARCHAR() columns limit their size to VARCHAR(768).

Pro tip: Avoid the LOB data types unless you absolutely need them. They make the server do extra work when you use them in SELECT or WHERE clauses. Use VARCHAR() instead.

Answer 2

VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns in a tabel) and the character set used. VARCHAR is easier to use.

TEXT data type can hold up to 64KB. Don't forget to escape the ' character (as in \') in TEXT field data.

Answer 3

you can use BLOB datatype to store your text. it can handle up to 64KB of data which is around 64000 characters.

Answer 4

Mediumtext has known bugs associated with inserting into a mediumtext field, so I would recommend staying away from that, as a paragraph is not enough to warrant it.