创建 SQL 变量字符列 > 255个字符支持多个数据库
我有一个通过用户选择的 ODBC 数据源存储数据的应用程序。到目前为止，它在一系列数据库系统（例如 JET、Oracle、SQL Server）上运行良好，因为 SQL 语法相当简单。
现在我遇到了一个问题，我需要在字符串中存储超过 255 个字符。之前我使用列类型 VARCHAR (255) 创建了表。
现在，如果我尝试使用例如 VARCHAR (512) 创建表，那么它会在 Access 数据库上失败。我知道我可以对 Access 使用 MEMO 类型，但这是非标准 SQL，因此在其他数据库系统（例如 Oracle）上可能会失败。
是否有任何广泛支持的 SQL 标准用于创建宽度超过 255 个字符的文本列，或者我是否需要寻找其他解决方案？在我看来，替代方案是：
1) 分析数据库系统并根据数据库系统自定义 SQL CREATE TABLE 命令。我不喜欢这个，因为它违背了使用 ODBC 的目的。
2）根据需要添加额外的255个字符的列（例如LONGSTRING1，LONGSTRING2，...）并在读取后连接。我不喜欢这个，因为这意味着表之间的列数可能会有所不同，并且会使读/写变得复杂。
除了这两种选择之外，还有其他可行的选择吗？或者是否有可能有一个大多数数据库供应商都支持的符合 SQL 标准的 CREATE TABLE 命令，并且支持长度超过 255 个字符的字符串？
I have an application that stores data through an ODBC data source of the user's choosing. So far it has worked well on a range of database systems (e.g. JET, Oracle, SQL Server), as the SQL syntax is fairly simple.
Now I am running into a problem where I need to store more than 255 characters in my strings. Previously I created the table using column type VARCHAR (255).
Now if I try to create a table using, e.g. VARCHAR (512) then it falls over on Access databases. I know that I can use the MEMO type for Access, but this is non-standard SQL and will thus likely fail on other database systems (e.g. Oracle).
Is there any widely supported SQL standard for creating text columns wider than 255 characters, or do I need to find another solution? The alternatives seem to me to be:
1) Profile the database system and customise the SQL CREATE TABLE command based on the database system. I don't like this as it defeats the purpose of using ODBC.
2) Add extra columns of 255 chars as required (e.g. LONGSTRING1, LONGSTRING2, ...) and concatenate after reading. I don't like this because it means the number of columns can vary between tables and it complicates read/write.
Are there any other viable alternatives to these two options? Or is it possible to have an SQL compliant CREATE TABLE command supported by the majority of database vendors, that supports strings longer than 255 chars?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
例如，从 Hibernate 中可以看出，它们为每个数据库都有一个配置，用于自定义如何创建未绑定的字符类型（除了许多其他数据库特定的自定义之外）。
No. As can be seen in Hibernate, for example, they have a config for every database which customizes how unbound character types are created (besides a lot of other database specific customizations).
IMO，选项#1 是你最好的选择。我怀疑数据类型语法将是您将遇到的唯一特定于数据库的异常。例如，许多数据库都有不同的方式来创建与 SQL Server 的 Identity 列等效的内容（例如，在 Access 中它是一个自动编号），假设它们完全支持该想法。正如其他人指出的那样，即使在同一产品的不同版本之间，您也可能无法使用相同的语法并使其正常工作。此外，如果 Jet 将成为受支持的数据库之一，我保证您将遇到大量语法问题，这些问题在其他数据库产品中有效，但在 Access 中不进行一些调整就不会。不幸的是，ISO 标准实际上仅意味着数据库之间的语法相似，而不是精确的。
IMO, Option #1 is your best option. I doubt that the data type syntax will be the only database specific anomaly that you will encounter. For example, many databases have a different way of creating the equivalent of SQL Server's Identity column (e.g. in Access it's an AutoNumber) assuming they support the idea at all. As others have pointed out, even between versions of the same product, you may not be able to use the same syntax and have it work. In addition, if Jet is going to be one of the supported databases, I guarantee you will run into plenty of syntax problems that work in other database products but not in Access without some tweaking. Unfortunately, the ISO standards really only mean that the syntax will be similar between databases not exact.
我可能被证明是错的，但我认为对于存储任意大小的二进制对象的字段（这就是大字符串字段的存储方式），没有适当的标准。
然而，与其根据您正在使用的数据库添加不同的列，不如使用一个单独的表，该表具有主键的 ID 键并将长文本存储在数据库特定字段中，这可能会更简洁。这将为您提供更大的灵活性。
附言。请不要选择选项 2。
I may be proved wrong, but I think there isn't a proper standard when it comes to fields that store arbitrary sized binary objects (which is what a large string field will be stored as).
However, rather than adding different columns based on the database you are using it might be neater to use a separate table which has an ID Key to your Primary Key and stores the long text in a database specific field. This will give you greater flexibility.
PS. Please don't go for option 2.