MySQL 输出屏蔽（即电话号码、SSN 等显示格式）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10112718/
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
MySQL output masking (i.e. phone number, SSN, etc. display formatting)
提问by Zak
Is there a built-in SQL function that will mask output data?
是否有内置的 SQL 函数可以屏蔽输出数据？
Let's say I have an integer column, that represents a phone number (for any country). Is there a better way to display the numbers than sub-stringing them apart, loading hashes, dashes and dot, then concatenating them back together?
假设我有一个整数列，它代表一个电话号码（适用于任何国家/地区）。有没有更好的方法来显示数字而不是将它们分开，加载散列，破折号和点，然后将它们连接在一起？
I know several languages have a feature to simply mask the data as it is displayed instead of restructuring it. Does MySQL have something similar?
我知道有几种语言有一个功能，可以在显示数据时简单地屏蔽数据，而不是对其进行重组。MySQL有类似的东西吗？
回答by Zak
Here's what I came up with, if you have any modifications or improvements please leave them as comments and I will update the code. Otherwise if you like it, don't for get to bump it. Enjoy!
这是我想出来的，如果你有任何修改或改进，请留下它们作为评论，我会更新代码。否则，如果您喜欢它，请不要碰它。享受！
DELIMITER //
CREATE FUNCTION mask (unformatted_value BIGINT, format_string CHAR(32))
RETURNS CHAR(32) DETERMINISTIC
BEGIN
# Declare variables
DECLARE input_len TINYINT;
DECLARE output_len TINYINT;
DECLARE temp_char CHAR;
# Initialize variables
SET input_len = LENGTH(unformatted_value);
SET output_len = LENGTH(format_string);
# Construct formated string
WHILE ( output_len > 0 ) DO
SET temp_char = SUBSTR(format_string, output_len, 1);
IF ( temp_char = '#' ) THEN
IF ( input_len > 0 ) THEN
SET format_string = INSERT(format_string, output_len, 1, SUBSTR(unformatted_value, input_len, 1));
SET input_len = input_len - 1;
ELSE
SET format_string = INSERT(format_string, output_len, 1, '0');
END IF;
END IF;
SET output_len = output_len - 1;
END WHILE;
RETURN format_string;
END //
DELIMITER ;
Here's how to use it... It only works for integers (i.e. SSN Ph# etc.)
这是如何使用它......它只适用于整数（即 SSN Ph# 等）
mysql> select mask(123456789,'###-##-####');
+-------------------------------+
| mask(123456789,'###-##-####') |
+-------------------------------+
| 123-45-6789 |
+-------------------------------+
1 row in set (0.00 sec)
mysql> select mask(123456789,'(###) ###-####');
+----------------------------------+
| mask(123456789,'(###) ###-####') |
+----------------------------------+
| (012) 345-6789 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> select mask(123456789,'###-#!##@(###)');
+----------------------------------+
| mask(123456789,'###-#!##@(###)') |
+----------------------------------+
| 123-4!56@(789) |
+----------------------------------+
1 row in set (0.00 sec)
回答by KiloVoltaire
Let's say your Peopletable has namelast, namefirst, and phonefields.
比方说，你的People表有namelast，namefirst和phone领域。
SELECT p.namelast, p.namefirst,
CONCAT(SUBSTR(p.phone,1,3), '-', SUBSTR(p.phone,4,3), '-', SUBSTR(p.phone,7,4)) AS Telephone,
FROM People p
The CONCAT(SUBSTR()) uses the fieldname for the first position, character position for the second, and number of digits for the third. Using "AS Telephone" keeps your column heading clean in your query output.
CONCAT(SUBSTR()) 使用字段名作为第一个位置，第二个使用字符位置，第三个使用数字位数。使用“AS 电话”可以使查询输出中的列标题保持干净。
One note of caution: I'd recommend against using integers for phone numbers, social security numbers, etc. The reason is that you'll probably never perform numeric computations with these numbers. While there is the odd chance you'd use them as a promary key, there's always the possibility of losing digits from numbers with leading zeros.
一个注意事项：我建议不要对电话号码、社会安全号码等使用整数。原因是您可能永远不会使用这些数字执行数字计算。虽然您很有可能将它们用作主键，但总是有可能会丢失带有前导零的数字中的数字。
回答by Brian Hoover
Yes, mySQL does have formatting power: http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_format
是的，mySQL 确实具有格式化能力：http: //dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_format
But you've also almost answered your own question. MySQL doesn't have the same amount of power to be able to easily write a formatter based on other criteria. It can, with if statements, but the logic makes the SQL difficult to read at best. So, if you were trying to have a formatter based on country code, for example, you would have to write a lot of logic into the SQL statement which isn't the best place for it, if you have the option of another language to parse the results.
但你也几乎回答了你自己的问题。MySQL 不具备根据其他标准轻松编写格式化程序的能力。它可以，使用 if 语句，但逻辑使 SQL 难以阅读。因此，例如，如果您尝试使用基于国家/地区代码的格式化程序，则必须在 SQL 语句中写入大量逻辑，这不是它的最佳位置，如果您可以选择另一种语言解析结果。
