SQLAlchemy 对“Text"使用什么列类型?在 MySQL 上? [英] What column type does SQLAlchemy use for "Text" on MySQL?

问题描述

我的整体用例试图确定我是否可以为一些大数据编写一个有点数据库不可知(至少支持 Postgres 和 MySQL)的存储作为原始文本(认为大约 500MB 作为一个粗略的理论上限).基于 this answer 关于 MySQL 字符串/文本类型，看起来只有 LONGTEXT 列类型可以满足我的要求.我正在使用 SQLAlchemy，它声称其 Text 列类型表示它是用于变长字符串，而且它通常映射到数据库的 CLOB 或 TEXT 类型.MySQL 没有 CLOB 类型(尽管它有 BLOB)，而且它的 TEXT 类型不足以满足我的需要.

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 answer about MySQL string/text types, it looks like only the LONGTEXT column type can meet my requirements. I'm using SQLAlchemy, which claims for its Text column 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.

那么，SQLAlchemy 对 MySQL 上的文本"使用什么列类型?

推荐答案

看起来 SQLAlchemy 支持 LONGTEXT:

Looks like SQLAlchemy supports 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
>>> 

在此处查看如何使用特定于供应商的类型:http://docs.sqlalchemy.org/en/latest/core/type_basics.html#vendor-specific-types

See how to use vendor-specific types here: http://docs.sqlalchemy.org/en/latest/core/type_basics.html#vendor-specific-types

就其价值而言，尝试开发一个完全中立的数据库层是困难的，而且很少值得付出努力.几年前我在 Zend Framework 1.0 上工作，我尝试为该框架支持的所有 SQL 数据库创建一个通用的单元测试套件.我发现所有 SQL 实现都以相同的方式支持很少的数据类型，尽管它们都声称支持 ANSI/ISO SQL 标准.

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.

最终，您必须为数据层开发自己的类层次结构，并为每个特定于数据库的适配器实现略有不同的代码.

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)

然后我检查了它最终在 MySQL 数据库中创建的内容:

Then I checked to see what it ended up creating in the MySQL database:

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

所以它确实将 SQLAlchemy 的通用 String 数据类型映射到或多或少合适的 MySQL 数据类型.

So it does map SQLAlchemy's generic String data type to a more-or-less appropriate MySQL data type.

它使用比我们预期的更大的数据类型并不奇怪.MEDIUMTEXT 支持 16MB 的字节，而不是字符.因为我的默认字符集是多字节 utfmb4，MEDIUMTEXT 的最大长度实际上远小于 2^24 个字符.所以它不得不将其升级为LONGTEXT.当然，LONGTEXT 也不适合 2^32 个字符，但似乎 SQLAlchemy 假设您打算创建一个列.

It's not surprising to me that it used larger data types than we might expect. The MEDIUMTEXT supports 16MB in bytes, not in characters. Because my default character set is the multi-byte utfmb4, the max length of MEDIUMTEXT is actually much fewer than 2^24 characters. So it had to upgrade it to LONGTEXT. Of course, 2^32 characters won't fit in LONGTEXT either, but it appears SQLAlchemy assumes you mean to create a column anyway.

我仍然认为很难编写完全与实现无关的代码.例如，如果您想使用某些 MySQL 功能，例如存储引擎的表选项，或没有通用等效项的特定数据类型(例如，ENUM)，该怎么办?

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)?

这篇关于SQLAlchemy 对“Text"使用什么列类型?在 MySQL 上?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！