使用mysql命令行客户端选择时如何查看数据中的空格

How can i see spaces in String when using select with the sql-command-line-client?

当使用带有sql-command-line-client的select时,如何在String中看到空格?

What i mean is the following. you have three lines. 1, 2 and 3 Spaces. You don't have a chance see the number of spaces.

我的意思是以下内容。你有三条线。 1,2和3个空格。你没有机会看到空格的数量。

create table foo(bar varchar(8));
insert into foo values(" "),("  "), ("   ");

select * from foo\g
+------+
| bar  |
+------+
|      |
|      |
|      |
+------+

mysql> select * from foo\G
*************************** 1. row ***************************
bar:  
*************************** 2. row ***************************
bar:   
*************************** 3. row ***************************
bar:    
3 rows in set (0.01 sec)

The only option i came up with is:

我想出的唯一选择是:

mysql> select bar, hex(bar) from foo;
+------+----------+
| bar  | hex(bar) |
+------+----------+
|      | 20       |
|      | 2020     |
|      | 202020   |
+------+----------+
3 rows in set (0.01 sec)

Something like var_export in php would be nice.

像php中的var_export这样的东西会很好。

4 个解决方案

#1

It appears there is a string-function QUOTE (Escape the argument for use in an SQL statement) wich works really nice.

它似乎有一个字符串函数QUOTE(转义在SQL语句中使用的参数),它非常好用。

-- to add another tricky example
mysql> insert into foo values(" \" ' "), ("''");
Query OK, 1 row affected (0.06 sec)

mysql> select bar, quote(bar) from foo;
+-------+------------+
| bar   | quote(bar) |
+-------+------------+
|       | ' '        |
|       | '  '       |
|       | '   '      |
|  " '  | ' " \' '   |
| ''    | '\'\''     |
+-------+------------+
4 rows in set (0.00 sec)

#2

You may replace spaces with some other characters in output like:

您可以使用输出中的其他字符替换空格,例如:

mysql> SELECT REPLACE('   ', ' ', '|');

+--------------------------+
| REPLACE('   ', ' ', '|') |
+--------------------------+
| |||                      |
+--------------------------+
1 row in set (0.00 sec)

#3

If this is just about displaying the space then you'll need to encode the string before you store it. If it's about comparison then you'll find that leading strings (before a non space) are preserved while trailing are not. See more here: here

如果这只是显示空间,那么在存储之前你需要对字符串进行编码。如果它是关于比较,那么你会发现保留前导字符串(在非空格之前)而不是尾随字符串。在这里看到更多:这里

#4

How about using String LENGTH() to get a similar result to php var_export()

如何使用String LENGTH()获取与php var_export()类似的结果

SELECT bar, LENGTH(bar) FROM foo;

#1