postgresql SQLAlchemy 对 MySQL 上的“文本”使用什么列类型？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/47644739/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What column type does SQLAlchemy use for "Text" on MySQL?
提问by augray
My overall use-case is trying to determine whether I can write a somewhat database agnostic (at least supporting Postgres and MySQL) store for some large data as raw text (think ~500MB as a rough theoretical upper bound). Based on this answerabout MySQL string/text types, it looks like only the LONGTEXT column type can meet my requirements. I'm using SQLAlchemy, which claims for its Textcolumn type that it is for variable length strings, but also that it generally maps to the database's CLOB or TEXT types. MySQL doesn't have a CLOB type (though it does have a BLOB), and it's TEXT type would be insufficient for my needs.
我的整体用例是试图确定我是否可以为一些大数据编写一个有点数据库不可知（至少支持 Postgres 和 MySQL）的存储作为原始文本（认为 ~500MB 作为一个粗略的理论上限）。基于这个关于 MySQL 字符串/文本类型的答案，看起来只有 LONGTEXT 列类型可以满足我的要求。我正在使用 SQLAlchemy，它声称其Text列类型适用于可变长度字符串，但它通常映射到数据库的 CLOB 或 TEXT 类型。MySQL 没有 CLOB 类型（尽管它有 BLOB），而且它的 TEXT 类型不足以满足我的需要。
So, What column type does SQLAlchemy use for "Text" on MySQL?
那么，SQLAlchemy 对 MySQL 上的“文本”使用什么列类型？
回答by Bill Karwin
Looks like SQLAlchemy supports LONGTEXT:
看起来 SQLAlchemy 支持 LONGTEXT：
$ python
Python 2.7.13 (default, Sep 29 2017, 15:31:18)
[GCC 4.2.1 Compatible Apple LLVM 9.0.0 (clang-900.0.37)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> from sqlalchemy.dialects.mysql import LONGTEXT
>>>
See how to use vendor-specific types here: http://docs.sqlalchemy.org/en/latest/core/type_basics.html#vendor-specific-types
在此处查看如何使用特定于供应商的类型：http: //docs.sqlalchemy.org/en/latest/core/type_basics.html#vendor-specific-types
For what it's worth, trying to develop a totally brand-neutral database layer is difficult, and rarely worth the effort. I worked on the Zend Framework 1.0 some years ago, and I tried to create a generic unit testing suite for all the SQL databases supported by that framework. I found that very few data types are supported in the same way across all implementations of SQL, despite them all claiming to support the ANSI/ISO SQL standard.
就其价值而言，尝试开发一个完全中立的数据库层是困难的，而且很少值得付出努力。几年前，我在 Zend Framework 1.0 上工作，并尝试为该框架支持的所有 SQL 数据库创建一个通用的单元测试套件。我发现所有 SQL 实现都以相同的方式支持很少的数据类型，尽管它们都声称支持 ANSI/ISO SQL 标准。
Ultimately, you have to develop your own class hierarchy for your data layer, and implement the code slightly differently for each database-specific adapter.
最终，您必须为数据层开发自己的类层次结构，并为每个特定于数据库的适配器实现略有不同的代码。
Update: I think the news is better than we think. I tried this test:
更新：我认为这个消息比我们想象的要好。我试过这个测试：
t2 = Table('t2', metadata,
Column('id', Integer, primary_key=True),
Column('t1', String(64000)),
Column('t2', String(16000000)),
Column('t3', String(4294000000)),
Column('t4', Text)
)
metadata.create_all(engine)
Then I checked to see what it ended up creating in the MySQL database:
然后我检查了它最终在 MySQL 数据库中创建的内容：
mysql> show create table t2;
CREATE TABLE `t2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`t1` mediumtext,
`t2` longtext,
`t3` longtext,
`t4` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
So it does map SQLAlchemy's generic Stringdata type to a more-or-less appropriate MySQL data type.
所以它确实将 SQLAlchemy 的通用String数据类型映射到或多或少合适的 MySQL 数据类型。
It's not surprising to me that it used larger data types than we might expect. The MEDIUMTEXTsupports 16MB in bytes, not in characters. Because my default character set is the multi-byte utfmb4, the max length of MEDIUMTEXTis actually much fewer than 2^24 characters. So it had to upgrade it to LONGTEXT. Of course, 2^32 characters won't fit in LONGTEXTeither, but it appears SQLAlchemy assumes you mean to create a column anyway.
对我来说，它使用比我们预期的更大的数据类型并不奇怪。该MEDIUMTEXT支持16MB的字节，而不是字符。因为我的默认字符集是多字节 utfmb4，所以最大长度MEDIUMTEXT实际上远小于 2^24 个字符。所以它不得不将它升级到LONGTEXT. 当然，2^32 个字符也不适合LONGTEXT，但似乎 SQLAlchemy 假设您无论如何都想创建一个列。
I still think that it's hard to do totally implementation-neutral code. For example, what if you want to use some MySQL features like table options for the storage engine, or specific data types with no generic equivalent (for example, ENUM)?
我仍然认为很难完全实现与实现无关的代码。例如，如果您想使用一些 MySQL 功能，例如存储引擎的表选项，或没有通用等效项的特定数据类型（例如，ENUM）怎么办？
回答by tandy
In SQLAlchemy 1.2.7, the Text data type maps to MySQL Type "text", or "longtext" depending on the length you enter.
在 SQLAlchemy 1.2.7 中，文本数据类型映射到 MySQL 类型“text”或“longtext”，具体取决于您输入的长度。
body_html = Column(Text())
body_plain = Column(Text(4294000000))
Creates the following in MySQL:
在 MySQL 中创建以下内容：
| Field | Type |
+-----------------+--------------+
| body_html | text |
| body_plain | longtext |
