Entries in my table are uniquely identified by word that is 5-10 characters long and I use TINYTEXT(10) for the column. However, when I try to set it as PRIMARY key I get the error that size
is missing.
From my limited understanding of the docs, Size
for PRIMARY keys can be used to simplify a way to detect unique value i.e. when first few character (specified by Size
) can be enough to consider it unique match. In my case, the size
would differ from 5 to 10 (they are all latin1 so they are exact byte per character + 1 for the lenght). Two questions:
- If i wanted to use TINYTEXT as PRIMARY key, which
size
should I specify? Maximum available - 10 in this case? Or should be thesize
strictly EXACT, for example, if my key is 6 character long word, but I specifySize
for PK as 10 - it will try to read all 10 and will fail and throw me an exception? - How bad performance-wise would be to use [TINY]TEXT for the PK? All Google results lead me to opinions and statements "it is BAD, you are fired", but is it really true in this case, considering TINYTEXT is 255 max and I already specified max length to 10?
3条答案
按热度按时间flseospp1#
The same trick can be done if you need to make primary key on a VARCHAR field which is larger than 3072 bytes, on BINARY fields and BLOBs. Anyway you can imagine that if two large and different texts start with the same characters at the first 3072 bytes at the beginning, they will be treated as equal by the system. That may be a problem.
2.1. It takes much more processing time than using integers to search in the table (WHERE, JOINS, etc). The link is old but still relevant;
2.2. Any foreign key in another table must have the same datatype as the primary key. When you use text, this will waste disk space;
Note: the difference between *TEXT and VARCHAR is that the contents of the *TEXT fields are not stored inside the table but in outside memory location. Usually we do that when we need to store really large text.
mbjcgjjk2#
for TINYTEXT can not specify the size. Use VARCHAR (size)
SQL Data Types
nr7wwzry3#
FYI, you can't specify a size for
TINYTEXT
in MySQL:You can specify a length after
TEXT
, but it doesn't work the way you think it does. It means it will choose one of the family of TEXT types, the smallest type that supports at least the length you requested. But once it does that, it does not limit the length of input. It still accepts any data up to the maximum length of the type it chose.