On Choosing Between text and varchar Types

This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 关于 text 和 varchar 类型选择

| username: 梦想是个厨子

Regarding TiDB table design and usage specifications, the choice between text and varchar is not clear here.

Link: 专栏 - TiDB库表设计和使用规范 | TiDB 社区

| username: WalterWj | Original post link

Large fields themselves are not recommended to be stored in the database. But I don’t understand which part is unclear?

| username: 梦想是个厨子 | Original post link

Why use varchar(10000) instead of the text type?

| username: xfworld | Original post link

The previous version didn’t support it very well…

If you have this requirement, you can use the new version to do a POC test.

| username: tidb菜鸟一只 | Original post link

This is also someone else’s suggestion, and I think it can be used now.

| username: Kongdom | Original post link

:joy: If the product supports both MySQL and TiDB databases, it is recommended to use text when creating tables. Using varchar(10000) will result in an error in MySQL… personal experience…

| username: zhanggame1 | Original post link

The maximum length of MySQL varchar is 65535, so 10000 should be fine.

| username: WalterWj | Original post link

Under the utf8mb4 character set encoding, one character occupies 4 bytes. I understand that varchar 10000 does not exceed the 6MB limit, right? Text itself does not have this limit, which is quite concerning.

| username: Kongdom | Original post link

:joy: There is more than one field in this table~
Look at this table, it’s both long and wide~

| username: Kongdom | Original post link

It’s not a large field either. For example, in our business, this field stores the values of a multi-select attribute, so we don’t know how much data it can hold. Some developers used to directly use varchar(max), but here there’s no max, so they use varchar(10000) or something like that. In reality, it might only use a length of a few dozen, but theoretically, someone could select multiple values reaching a length of tens of thousands.

| username: liuis | Original post link

Using varchar for very large fields is not suitable; it will directly report an error when inserting.

| username: WalterWj | Original post link

Calculate the max length to know how big it is, and make adjustments. It is generally not recommended to store large fields in a relational database.

| username: Kongdom | Original post link

No need to calculate, we are working on a project. Just run the script on the project to see if it exceeds the length. Haha, if it exceeds, just change it to text.

| username: zhanggame1 | Original post link

It is still recommended to set it according to business needs. Setting it too large is not very good.

| username: Kongdom | Original post link

Yes, that’s right.

| username: liuis | Original post link

Sometimes it’s hard to predict this thing, and it might throw an error one day. It will be quite troublesome to fix it then.

| username: Hacker007 | Original post link

TiDB has a maximum size limit of 6MB for a single row and column, which is also to prevent rows from becoming too large.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.