char字段可以保留尾部空格字符吗？ [英] Can char field retain trailing space character?

问题描述

我有一个主键为PremiseID的数据库表。

I have a database table with a primary key called PremiseID.

其MySQL列定义为CHAR（10）。

Its MySQL column definition is CHAR(10).

进入列的数据始终为10位数字，后跟空格，如113091000或9位数字后面跟一个字母，如'113091000A'。

The data that goes into the column is always 10 digits, which is either a 9-digit number followed by a space, like '113091000 ' or a 9-digit number followed by a letter, like '113091000A'.

我已经尝试将这些值写入测试MySQL数据库表t1的表中。它有三列

I've tried writing one of these values into a table in a test MySQL database table t1. It has three columns

mainid integer
parentid integer
premiseid char(10)

如果我插入一行具有以下值： 1,1，'113091000' code>并尝试读取行，'113991000'值被截断，因此它读取'113091000';即空间被删除。如果我插入一个数字，如'113091000A'，该值保留。

If I insert a row that has the following values: 1,1,'113091000 ' and try to read row back, the '113991000 ' value is truncated, so it reads '113091000'; that is the space is removed. If I insert a number like '113091000A', that value is retained.

如何获得CHAR（10）字段保留空格字符？

How can I get the CHAR(10) field retain the space character?

我有一个编程方式解决这个问题。这将是len（'113091000'），实现它的九个字符，然后实现长度的9个infers有一个空格后缀的数字。

I have a programmatic way around this problem. It would be to take the len('113091000'), realize it's nine characters, and then realize a length of 9 infers there is a space suffix for that number.

推荐答案

引用 MySQL参考：

CHAR 列的长度固定为您在创建表时声明。长度可以是0到255之间的任何值。存储 CHAR 值时，它们用指定长度的空格进行右侧填充。当检索 CHAR 值时，将删除尾随空格。

The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed.

方式。如果你使用MySQL 5.0.3或更高版本，那么使用 VARCHAR 可能是最好的方法（开销只有1个额外的字节）：

So there's no way around it. If you're using MySQL 5.0.3 or greater, then using VARCHAR is probably the best way to go (the overhead is only 1 extra byte):

VARCHAR 值在存储时不进行填充。尾部空格的处理取决于版本。从MySQL 5.0.3起，根据标准SQL存储和检索值时，会保留尾随空格。在MySQL 5.0.3之前，当值存储到 VARCHAR 列中时，会从值中删除尾随空格;

VARCHAR values are not padded when they are stored. Handling of trailing spaces is version-dependent. As of MySQL 5.0.3, trailing spaces are retained when values are stored and retrieved, in conformance with standard SQL. Before MySQL 5.0.3, trailing spaces are removed from values when they are stored into a VARCHAR column; this means that the spaces also are absent from retrieved values.

如果您使用的是MySQL< 5.0.3，那么我想你只需检查返回的长度，或使用一个空格以外的字符。

If you're using MySQL < 5.0.3, then I think you just have to check returned lengths, or use a character other than a space.

可能最便携的解决方案是使用 CHAR 并检查返回的长度。

Probably the most portable solution would be to just use CHAR and check the returned length.

这篇关于char字段可以保留尾部空格字符吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！