在MySQL中，BLOB和TEXT类型用于存储大对象数据。如果在非严格SQL模式下，赋值超过列最大长度，数据会被截断并产生警告。启用严格模式可将截断错误转换为错误，防止插入。VARCHAR字段在某些场景下优于TEXT，因为它们在MyISAM存储引擎中存储在行内，减少磁盘读取，且对于包含排序的查询，使用VARCHAR可能避免磁盘临时表。
正如在MySQL文档中有关BLOB和TEXT的内容一样.
11.4.3 BLOB和TEXT类型
If strict SQL mode is not enabled and you assign a value to a BLOB or TEXT column that exceeds the column’s maximum length, the value is truncated to fit and a warning is generated. For truncation of nonspace characters, you can cause an error to occur (rather than a warning) and suppress insertion of the value by using strict SQL mode.
这可以解释数据丢失.
>因此,如从我的评论中所述,需要从TEXT到VARCHAR进行完全重建：
“you may have to rebuild your hashes then”
我发现与此有关的另一个链接：
从该页面部分检索：
Starting with MySQL 5.0.3, the maximum field length for VARCHAR fields was increased from 255 characters to 65,535 characters. This is good news, as VARCHAR fields, as opposed to TEXT fields, are stored in-row for the MyISAM storage engine (InnoDB has different characteristics). TEXT and BLOB fields are not stored in-row — they require a separate lookup (and a potential disk read) if their column is included in a SELECT clause. Additionally, the inclusion of a TEXT or BLOB column in any sort will force the sort to use a disk-based temporary table, as the MEMORY (HEAP) storage engine, which is used for temporary tables, requires.
Thus, the benefits of using a VARCHAR field instead of TEXT for columns between 255 and 65k characters seem obvious at first glance in some scenarios: potentially less disk reads (for queries including the column, as there is no out-of-row data) and less writes (for queries with sorts including the column).
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
