{"id":2118,"date":"2015-05-01T00:29:34","date_gmt":"2015-04-30T16:29:34","guid":{"rendered":"http:\/\/ixyzero.com\/blog\/?p=2118"},"modified":"2015-05-01T00:29:34","modified_gmt":"2015-04-30T16:29:34","slug":"mysql%e4%b8%ad%e7%9a%84charvarchar%e5%92%8cbinaryvarbinary","status":"publish","type":"post","link":"https:\/\/ixyzero.com\/blog\/archives\/2118.html","title":{"rendered":"MySQL\u4e2d\u7684char\/varchar\u548cbinary\/varbinary"},"content":{"rendered":"<h6>\u5b98\u65b9\u6587\u6863\uff1a<\/h6>\n<ul>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/binary-varbinary.html\" target=\"_blank\">MySQL :: MySQL 5.5 Reference Manual :: 11.4.2 The BINARY and VARBINARY Types<\/a><\/li>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/char.html\" target=\"_blank\">MySQL :: MySQL 5.5 Reference Manual :: 11.4.1 The CHAR and VARCHAR Types<\/a><\/li>\n<\/ul>\n<p>==varbinary\u7c7b\u578b\u7684\u503c\u6bd4\u8f83\u65f6\u533a\u5206\u5927\u5c0f\u5199\uff1b\u4f5c\u4e3a\u7d22\u5f15\u7684\u8bdd\u901f\u5ea6\u8f83varchar\u5feb\uff1b\u800c\u4e14\u4e0d\u4f1a\u5bf9\u539f\u59cb\u6570\u636e\u8fdb\u884c\u4fee\u6539==<\/p>\n<p>The BINARY\u00a0and VARBINARY\u00a0types are similar to <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/char.html\">CHAR<\/a>\u00a0and <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/char.html\">VARCHAR<\/a>, except that they contain binary strings rather than nonbinary strings.\uff08MySQL\u7684binary\u548cvarbinary\u7c7b\u578b &amp; char\u548cvarchar\u7c7b\u578b \u662f\u975e\u5e38\u50cf\u7684\uff0c\u9664\u4e86[var]binary\u5b58\u653e\u7684\u662f\u4e8c\u8fdb\u5236\u5b57\u7b26\u4e32\u4ee5\u5916\uff09 That is, they contain byte strings rather than character strings.\uff08\u5b83\u4eec\u5305\u542b\u7684\u662f\u5b57\u8282\u4e32\u800c\u4e0d\u662f\u5b57\u7b26\u4e32\uff09 This means that they have no character set, and sorting and comparison are based on the numeric values of the bytes in the values.\uff08\u8fd9\u610f\u5473\u7740\u5b83\u4eec\u6ca1\u6709\u5b57\u7b26\u96c6\u7684\u6982\u5ff5\uff0c\u56e0\u6b64\u6392\u5e8f\u548c\u6bd4\u8f83\u90fd\u662f\u57fa\u4e8e\u5b57\u8282\u4e2d\u7684\u5b9e\u9645\u6570\u503c\u5927\u5c0f\u8fdb\u884c\u7684\uff09<\/p>\n<p>The permissible maximum length is the same for BINARY\u00a0and VARBINARY\u00a0as it is for <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/char.html\">CHAR<\/a>\u00a0and<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/char.html\">VARCHAR<\/a>, except that the length for BINARY\u00a0and VARBINARY\u00a0is a length in bytes rather than in characters.\uff08binary\u548cvarbinary\u7684\u5141\u8bb8\u7684\u6700\u5927\u957f\u5ea6\u548cchar\/varchar\u7c7b\u578b\u662f\u4e00\u6837\u7684\uff0c\u9664\u4e86binary\/varbinary\u7684\u957f\u5ea6\u6307\u7684\u662f\u5b57\u8282\u4e32\u7684\u957f\u5ea6\u4e4b\u5916\uff09<\/p>\n<p>The BINARY\u00a0and VARBINARY\u00a0data types are distinct from the CHAR BINARY\u00a0and VARCHAR BINARY\u00a0data types.\uff08binary\/varbinary\u7c7b\u578b\u548cchar binary\/varchar binary\u7c7b\u578b\u662f\u4e0d\u540c\u7684\uff09 For the latter types, the BINARY\u00a0attribute does not cause the column to be treated as a binary string column. Instead, it causes the binary collation for the column character set to be used, and the column itself contains nonbinary character strings rather than binary byte strings. For example, <strong>CHAR(5) BINARY is treated as CHAR(5) CHARACTER SET latin1 COLLATE latin1_bin<\/strong>, assuming that the default character set is latin1. This differs from BINARY(5), which stores 5-bytes binary strings that have no character set or collation. For information about <strong>differences between nonbinary string binary collations and binary strings<\/strong>, see <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/charset-binary-collations.html\">Section 10.1.7.6, \u201cThe _bin and binary Collations\u201d<\/a>.<\/p>\n<p>If strict SQL mode is not enabled and you assign a value to a BINARY\u00a0or VARBINARY\u00a0column that exceeds the column&#8217;s maximum length, the value is truncated to fit and a warning is generated.\uff08\u5728\u975e\u4e25\u683c\u7684SQL\u6a21\u5f0f\u4e0b\uff0c\u5982\u679c\u4e8b\u5148\u957f\u5ea6\u6ca1\u6709\u6307\u5b9a\u5bf9\uff0c\u8d85\u8fc7\u7684\u90e8\u5206\u4f1a\u88ab\u622a\u65ad\uff0c\u540c\u65f6\u4ea7\u751fwarning\uff09 For cases of truncation, you can cause an error to occur (rather than a warning) and suppress insertion of the value by using strict SQL mode. See<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/sql-mode.html\">Section 5.1.7, \u201cServer SQL Modes\u201d<\/a>.<\/p>\n<p>When BINARY\u00a0values are stored, they are right-padded with the pad value to the specified length. The pad value is 0x00\u00a0(the zero byte). Values are right-padded with 0x00\u00a0on insert, and no trailing bytes are removed on select. All bytes are significant in comparisons, including ORDER BY\u00a0and DISTINCT\u00a0operations. <strong>0x00\u00a0bytes and spaces are different in comparisons, with 0x00\u00a0&lt; space<\/strong>.\uff08binary\u7c7b\u578b\u7684\u503c\u5728<span style=\"color: #ff0000;\"><strong>\u5b58\u50a8<\/strong><\/span>\u7684\u65f6\u5019\u662f\u4f1a\u5bf9\u4e0d\u8db3\u7684\u5730\u65b9\u75280x00\u5728\u53f3\u4fa7\u8fdb\u884c\u586b\u5145\u7684\uff0c\u88ab\u586b\u5145\u4e86\u4e4b\u540e\uff0c\u201c\u503c\u201d\u5728\u8fdb\u884c\u6bd4\u8f83\u7684\u65f6\u5019\u662f\u4f1a\u628a0x00\u5e26\u4e0a\u7684\uff09<\/p>\n<p>Example: For a BINARY(3)\u00a0column, &#8216;a &#8216;\u00a0becomes &#8216;a \\0&#8217;\u00a0when inserted. &#8216;a\\0&#8217;\u00a0becomes &#8216;a\\0\\0&#8217;\u00a0when inserted. Both inserted values remain unchanged when selected.<\/p>\n<p>For VARBINARY, there is no padding on insert and no bytes are stripped on select. <strong>All bytes are significant in comparisons<\/strong>, including ORDER BY\u00a0and DISTINCT\u00a0operations. 0x00\u00a0bytes and spaces are different in comparisons, with 0x00\u00a0&lt; space.\uff08varbinary\u7c7b\u578b\u548cbinary\u7c7b\u578b\u662f\u4e0d\u540c\u7684\uff0cvarbinary\u662f\u4e0d\u4f1a\u8fdb\u884c\u586b\u5145\u64cd\u4f5c\u7684\uff0c\u8be5\u662f\u600e\u4e48\u6837\u5c31\u662f\u600e\u4e48\u6837\uff09<\/p>\n<p>For those cases where trailing pad bytes are stripped or comparisons ignore them, if a column has an index that requires unique values, inserting into the column values that differ only in number of trailing pad bytes will result in a duplicate-key error. For example, if a table contains &#8216;a&#8217;, an attempt to store &#8216;a\\0&#8217;\u00a0causes a duplicate-key error.\uff08\u5982\u679c\u67d0\u4e00\u6709\u7d22\u5f15\u7684\u5217\u9700\u8981\u552f\u4e00\u7684\u503c\uff0c\u5728\u540c\u4e00\u5217\u4e2d\u63d2\u5165\u53ea\u5728\u672b\u5c3e\u586b\u5145\u5b57\u7b26\u6709\u4e9b\u8bb8\u5dee\u522b\u7684\u503c\u65f6\uff0c\u4f1a\u5f15\u8d77duplicate-key\u7684\u9519\u8bef\uff09<\/p>\n<p>You should consider the preceding padding and stripping characteristics carefully if you plan to use theBINARY\u00a0data type for storing binary data and you require that the value retrieved be exactly the same as the value stored. The following example illustrates how 0x00-padding of BINARY\u00a0values affects column value comparisons:<\/p>\n<pre class=\"programlisting\">mysql&gt; <strong class=\"userinput\"><code>CREATE TABLE t (c BINARY(3));<\/code><\/strong>\nQuery OK, 0 rows affected (0.01 sec)\n\nmysql&gt; <strong class=\"userinput\"><code>INSERT INTO t SET c = 'a';<\/code><\/strong>\nQuery OK, 1 row affected (0.01 sec)\n\nmysql&gt; <strong class=\"userinput\"><code>SELECT HEX(c), c = 'a', c = 'a\\0\\0' from t;<\/code><\/strong>\n+--------+---------+-------------+\n| HEX(c) | c = 'a' | c = 'a\\0\\0' |\n+--------+---------+-------------+\n| 610000 |       0 |           1 |\n+--------+---------+-------------+\n1 row in set (0.09 sec)<\/pre>\n<p>If the value retrieved must be the same as the value specified for storage with no padding, it might be preferable to useVARBINARY\u00a0or one of the <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/blob.html\">BLOB<\/a>\u00a0data types instead.\uff08\u5982\u679c\u503c\u5728\u8fdb\u884c\u68c0\u7d22\u7684\u65f6\u5019\u9700\u8981\u548c\u672a\u586b\u5145\u4e4b\u524d\u76f8\u540c\u7684\u8bdd\uff0c\u5efa\u8bae\u4f7f\u7528varbinary\u6216\u8005\u67d0\u4e00\u4e2ablob\u7c7b\u578b\u66ff\u4ee3\uff0c\u800c\u4e0d\u8981\u4f7f\u7528binary\u7c7b\u578b\uff09<\/p>\n<p>==<\/p>\n<p>The CHAR\u00a0and VARCHAR\u00a0types are similar, but differ in the way they are stored and retrieved.\uff08char\u548cvarchar\u7c7b\u578b\u662f\u76f8\u4f3c\u7684\uff0c\u4f46\u5728 <strong>\u5b58\u50a8<\/strong> &amp; <strong>\u68c0\u7d22<\/strong> \u7684\u65b9\u5f0f\u4e0a\u6709\u6240\u4e0d\u540c\uff09 They also differ in maximum length and in whether trailing spaces are retained.\uff08\u5b83\u4eec\u5728\u6700\u5927\u957f\u5ea6\u548c\u662f\u5426\u4fdd\u7559\u5c3e\u90e8\u7a7a\u683c\u8fd9\u4e24\u70b9\u4e0a\u6709\u6240\u4e0d\u540c\uff09<\/p>\n<p>The CHAR\u00a0and VARCHAR\u00a0types are declared with a length that indicates the maximum number of characters you want to store. For example, CHAR(30)\u00a0can hold up to 30 characters.\uff08char\u548cvarchar\u5728\u58f0\u660e\u7684\u65f6\u5019\u9700\u8981\u6307\u660e\u80fd\u591f\u5b58\u653e\u7684\u6700\u5927\u957f\u5ea6\uff09<\/p>\n<p>The length of a CHAR\u00a0column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255.\uff08char\u90a3\u4e00\u5217\u7684\u957f\u5ea6\u5728\u4f60\u521b\u5efa\u8868\u7684\u65f6\u5019\u5c31\u5df2\u7ecf\u88ab\u6307\u5b9a\u4e86\uff0c\u53ef\u4ee5\u4e3a<strong>0-255<\/strong>\u4e4b\u95f4\u7684\u4efb\u4f55\u503c\uff09\u00a0When\u00a0CHAR\u00a0values are stored, they are right-padded with spaces to the specified length.\uff08<strong>char\u7c7b\u578b\u7684\u503c\u5728\u5b58\u50a8\u7684\u65f6\u5019\u662f\u4f1a\u5728\u6700\u53f3\u7aef\u7528\u7a7a\u683c\u586b\u5145\u4e0d\u8db3\u7684\u90e8\u5206\u7684<\/strong>\uff09 When\u00a0CHAR\u00a0values are retrieved, trailing spaces are removed unless the\u00a0<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/sql-mode.html#sqlmode_pad_char_to_full_length\">PAD_CHAR_TO_FULL_LENGTH<\/a>\u00a0SQL mode is enabled.\uff08\u4f46\u5728\u68c0\u7d22char\u7c7b\u578b\u7684\u503c\u65f6\uff0c\u9ed8\u8ba4\u60c5\u51b5\u4e0b\u662f\u4f1a\u5148\u5c06\u5c3e\u90e8\u7684\u7a7a\u683c\u7ed9\u53bb\u6389\u7136\u540e\u6bd4\u8f83\u7684\uff0c\u9664\u975e\u8bbe\u7f6e\u4e86<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/sql-mode.html#sqlmode_pad_char_to_full_length\">PAD_CHAR_TO_FULL_LENGTH<\/a>\u6a21\u5f0f\uff09<\/p>\n<p>Values in VARCHAR\u00a0columns are variable-length strings. The length can be specified as a value from <strong>0 to 65,535<\/strong>. The effective maximum length of a VARCHAR\u00a0is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used. See <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/column-count-limit.html\">Section D.10.4, \u201cLimits on Table Column Count and Row Size\u201d<\/a>.<\/p>\n<p>In contrast to CHAR, VARCHAR\u00a0values are stored as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.\uff08\u4e0echar\u7c7b\u578b\u76f8\u6bd4\uff0cvarchar\u7c7b\u578b\u7684\u503c\u9700\u89811\u52302\u4e2a\u5b57\u8282\u7684\u7a7a\u95f4\u5b58\u653e\u9664\u4e86\u6570\u636e\u4e4b\u5916\u7684\u957f\u5ea6\u4fe1\u606f\uff1b\u5f53\u503c\u7684\u5927\u5c0f\u57280-255\u4e4b\u95f4\u65f6\uff0c\u53ea\u9700\u89811\u5b57\u8282\uff0c\u5426\u5219\u9700\u89812\u4e2a\u5b57\u8282\uff09<\/p>\n<p>If strict SQL mode is not enabled and you assign a value to a CHAR\u00a0or VARCHAR\u00a0column that exceeds the column&#8217;s maximum length, the value is truncated to fit and a warning is generated. For truncation of nonspace characters, you can cause an error to occur (rather than a warning) and suppress insertion of the value by using strict SQL mode. See <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/sql-mode.html\">Section 5.1.7, \u201cServer SQL Modes\u201d<\/a>.\uff08\u5982\u679c\u4e0d\u662f\u4e25\u683c\u7684sql\u6a21\u5f0f\uff0c\u5b58\u653e\u7684\u5185\u5bb9\u5927\u5c0f\u8d85\u51fa\u4e86\u5217\u58f0\u660e\u7684\u5927\u5c0f\u7684\u8bdd\uff0c\u591a\u4f59\u7684\u90e8\u5206\u4f1a\u88ab\u622a\u65ad\u540c\u65f6\u4ea7\u751fwarning\uff0c\u4f46\u4e0d\u4f1a\u62a5\u9519\uff09<\/p>\n<p>For VARCHAR\u00a0columns, trailing spaces in excess of the column length are truncated prior to insertion and a warning is generated, regardless of the SQL mode in use. For CHAR\u00a0columns, truncation of excess trailing spaces from inserted values is performed silently regardless of the SQL mode.<\/p>\n<p>VARCHAR\u00a0values are not padded when they are stored. Trailing spaces are retained when values are stored and retrieved, in conformance with standard SQL.\uff08varchar\u7c7b\u578b\u7684\u503c\u4e0d\u4f1a\u5728\u5c3e\u90e8\u8fdb\u884c\u586b\u5145\u5904\u7406\uff1b\u4f46\u5982\u679c\u503c\u7684\u5c3e\u90e8\u672c\u6765\u662f\u6709\u7a7a\u683c\u7684\u8bdd\uff0c\u5c3e\u90e8\u7684\u7a7a\u683c\u4e5f\u4e0d\u4f1a\u88ab\u5220\u9664\uff09<\/p>\n<p>The following table illustrates the differences between CHAR\u00a0and VARCHAR\u00a0by showing the result of storing various string values into CHAR(4)\u00a0and VARCHAR(4)\u00a0columns (assuming that the column uses a single-byte character set such as latin1).<\/p>\n<table>\n<thead>\n<tr>\n<th>Value<\/th>\n<th>CHAR(4)<\/th>\n<th>Storage Required<\/th>\n<th>VARCHAR(4)<\/th>\n<th>Storage Required<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>&#8221;<\/td>\n<td>&#8216;\u00a0\u00a0\u00a0 &#8216;<\/td>\n<td>4 bytes<\/td>\n<td>&#8221;<\/td>\n<td>1 byte<\/td>\n<\/tr>\n<tr>\n<td>&#8216;ab&#8217;<\/td>\n<td>&#8216;ab\u00a0 &#8216;<\/td>\n<td>4 bytes<\/td>\n<td>&#8216;ab&#8217;<\/td>\n<td>3 bytes<\/td>\n<\/tr>\n<tr>\n<td>&#8216;abcd&#8217;<\/td>\n<td>&#8216;abcd&#8217;<\/td>\n<td>4 bytes<\/td>\n<td>&#8216;abcd&#8217;<\/td>\n<td>5 bytes<\/td>\n<\/tr>\n<tr>\n<td>&#8216;abcdefgh&#8217;<\/td>\n<td>&#8216;abcd&#8217;<\/td>\n<td>4 bytes<\/td>\n<td>&#8216;abcd&#8217;<\/td>\n<td>5 bytes<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><strong>The values shown as stored in the last row of the table apply <em>only when not using strict mode<\/em><\/strong>; if MySQL is running in strict mode, values that exceed the column length are <em>not stored<\/em>, and an error results.<\/p>\n<p>If a given value is stored into the CHAR(4)\u00a0and VARCHAR(4)\u00a0columns, the values retrieved from the columns are not always the same because trailing spaces are removed from CHAR\u00a0columns upon retrieval.\uff08\u5728\u8fdb\u884c\u68c0\u7d22\u7684\u65f6\u5019\uff0cchar\u548cvarchar\u7c7b\u578b\u7684\u6570\u636e\u4e0d\u603b\u662f\u76f8\u540c\u7684\uff0c\u56e0\u4e3a\u5c3e\u90e8\u7684\u7a7a\u683c\u4f1a\u88ab\u5220\u9664\uff09 The following example illustrates this difference:<\/p>\n<pre class=\"programlisting\">mysql&gt; <strong class=\"userinput\"><code>CREATE TABLE vc (v VARCHAR(4), c CHAR(4));<\/code><\/strong>\nQuery OK, 0 rows affected (0.01 sec)\n\nmysql&gt; <strong class=\"userinput\"><code>INSERT INTO vc VALUES ('ab  ', 'ab  ');<\/code><\/strong>\nQuery OK, 1 row affected (0.00 sec)\n\nmysql&gt; <strong class=\"userinput\"><code>SELECT CONCAT('(', v, ')'), CONCAT('(', c, ')') FROM vc;<\/code><\/strong>\n+---------------------+---------------------+\n| CONCAT('(', v, ')') | CONCAT('(', c, ')') |\n+---------------------+---------------------+\n| (ab  )              | (ab)                |\n+---------------------+---------------------+\n1 row in set (0.06 sec)<\/pre>\n<p>Values in CHAR\u00a0and VARCHAR\u00a0columns are sorted and compared according to the character set collation assigned to the column.\uff08<strong>\u7528char\u548cvarchar\u7c7b\u578b\u5b58\u50a8\u7684\u5217\u5728\u6bd4\u8f83\u7684\u65f6\u5019\u662f\u901a\u8fc7\u5b57\u7b26\u96c6\u7684\u65b9\u5f0f\u8fdb\u884c\u7684<\/strong>\uff09<\/p>\n<p>All MySQL collations are of type PADSPACE. This means that <strong>all CHAR, VARCHAR, and TEXT\u00a0values in MySQL are compared without regard to any trailing spaces<\/strong>.\uff08<span style=\"color: #ff0000;\"><strong>\u6240\u6709\u7684char\u3001varchar\u548ctext\u7c7b\u578b\u7684\u503c\u5728\u8fdb\u884c\u6bd4\u8f83\u7684\u65f6\u5019\u662f\u4e0d\u8003\u8651\u5c3e\u90e8\u7684\u7a7a\u683c\u7684\uff1b\u6709\u4e00\u70b9\u4f8b\u5916\u5c31\u662f\u5728\u4f7f\u7528like\u8fdb\u884c\u6a21\u5f0f\u5339\u914d\u7684\u65f6\u5019\u5c3e\u90e8\u7684\u7a7a\u683c\u662f\u6709\u610f\u4e49\u7684\uff0c\u4f1a\u8003\u8651\u5728\u5185<\/strong><\/span>\uff09 <span style=\"font-style: inherit; font-weight: inherit;\">\u201c<span style=\"font-style: inherit; font-weight: inherit;\">Comparison<\/span>\u201d<\/span>\u00a0in this context does not include the <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/string-comparison-functions.html#operator_like\">LIKE<\/a>\u00a0pattern-matching operator, for which trailing spaces are significant. For example:<\/p>\n<pre class=\"programlisting\">mysql&gt; <strong class=\"userinput\"><code>CREATE TABLE names (myname CHAR(10));<\/code><\/strong>\nQuery OK, 0 rows affected (0.03 sec)\n\nmysql&gt; <strong class=\"userinput\"><code>INSERT INTO names VALUES ('Monty');<\/code><\/strong>\nQuery OK, 1 row affected (0.00 sec)\n\nmysql&gt; <strong class=\"userinput\"><code>SELECT myname = 'Monty', myname = 'Monty  ' FROM names;<\/code><\/strong>\n+------------------+--------------------+\n| myname = 'Monty' | myname = 'Monty  ' |\n+------------------+--------------------+\n|                1 |                  1 |\n+------------------+--------------------+\n1 row in set (0.00 sec)\n\nmysql&gt; <strong class=\"userinput\"><code>SELECT myname LIKE 'Monty', myname LIKE 'Monty  ' FROM names;<\/code><\/strong>\n+---------------------+-----------------------+\n| myname LIKE 'Monty' | myname LIKE 'Monty  ' |\n+---------------------+-----------------------+\n|                   1 |                     0 |\n+---------------------+-----------------------+\n1 row in set (0.00 sec)<\/pre>\n<p><strong>This is true for all MySQL versions, and is not affected by the server SQL mode<\/strong>.<\/p>\n<blockquote>\n<h6 style=\"padding-left: 30px;\">Note<\/h6>\n<p style=\"padding-left: 30px;\">For more information about MySQL character sets and collations, see <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/charset.html\">Section 10.1, \u201cCharacter Set Support\u201d<\/a>. For additional information about storage requirements, see <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/storage-requirements.html\">Section 11.7, \u201cData Type Storage Requirements\u201d<\/a>.<\/p>\n<\/blockquote>\n<p>For those cases where trailing pad characters are stripped or comparisons ignore them, if a column has an index that requires unique values, inserting into the column values that differ only in number of trailing pad characters will result in a duplicate-key error. For example, if a table contains &#8216;a&#8217;, an attempt to store &#8216;a &#8216;\u00a0causes a duplicate-key error.\uff08\u5f53\u5c3e\u90e8\u7684\u586b\u5145\u5b57\u7b26\u88ab\u53bb\u6389\u4e4b\u540e\u6216\u8005\u5728\u6bd4\u8f83\u7684\u65f6\u5019\u5ffd\u7565\u5b83\u4eec\u65f6\uff0c\u5982\u679c\u67d0\u4e00\u5217\u6709\u4e00\u4e2a\u7d22\u5f15\u9700\u8981\u552f\u4e00\u503c\uff0c\u4f46\u63d2\u5165\u5217\u4e2d\u7684\u503c\u53ea\u5728\u5c3e\u90e8\u7684\u7a7a\u683c\u6570\u91cf\u4e0a\u4e0d\u540c\u7684\u8bdd\uff0c\u5c31\u4f1a\u5f15\u53d1duplicate-key\u9519\u8bef\uff09<\/p>\n<p>==<\/p>\n<p>\u603b\u7ed3\u8d77\u6765\u5c31\u662f\uff0c\u4f7f\u7528char\u7c7b\u578b\u5b58\u653e\u67d0\u4e00\u503c\u65f6\uff0c\u5982\u679c\u8be5\u503c\u7684\u672b\u5c3e\u5305\u542b\u7a7a\u683c\uff0c\u90a3\u4e48MySQL\u5728\u8fdb\u884c\u64cd\u4f5c\uff08\u663e\u793a\u3001\u6bd4\u8f83\u2026\u2026\uff09\u7684\u65f6\u5019\u5c31\u4f1a\u5148\u5c06\u5c3e\u90e8\u7684\u7a7a\u683c\u7ed9\u5220\u9664\uff0c\u4f46\u5728\u5b58\u50a8\u7684\u65f6\u5019\u662f\u4f1a\u5728\u6700\u540e\u7528\u7a7a\u683c\u8fdb\u884c\u586b\u5145\uff1b\u7528varchar\u7684\u597d\u5904\u5c31\u662f\uff0c\u4f60\u7ed9\u6211\u4ec0\u4e48\uff0c\u6211\u5c31\u5b58\u4ec0\u4e48\uff0c\u4e0d\u4f5c\u4efb\u4f55\u989d\u5916\u7684\u64cd\u4f5c\uff08\u4f46\u5728\u8fdb\u884c\u6bd4\u8f83\u7684\u65f6\u5019&#8217;a &#8216;\u548c&#8217;a&#8217;\u88ab\u8ba4\u4e3a\u662f\u76f8\u540c\u7684\uff09\u3002<\/p>\n<p>====<\/p>\n<h5><strong>\u4e3a\u4ec0\u4e48\u73b0\u5728\u5927\u591a\u6570\u60c5\u51b5\u4e0b\u4eba\u4eec\u7528varbinary\u66ff\u4ee3varchar\uff1f<\/strong><\/h5>\n<h6>\u641c\u7d22\u5173\u952e\u5b57\uff1a<\/h6>\n<ul>\n<li>MySQL varbinary vs varchar<\/li>\n<li>MySQL varbinary<\/li>\n<\/ul>\n<h6>\u53c2\u8003\u94fe\u63a5\uff1a<\/h6>\n<ul>\n<li><a href=\"http:\/\/stackoverflow.com\/questions\/973923\/mysql-varbinary-vs-varchar\">http:\/\/stackoverflow.com\/questions\/973923\/mysql-varbinary-vs-varchar<\/a><\/li>\n<li><a href=\"http:\/\/stackoverflow.com\/questions\/13393718\/why-varbinary-instead-of-varchar\">http:\/\/stackoverflow.com\/questions\/13393718\/why-varbinary-instead-of-varchar<\/a><\/li>\n<li><a href=\"http:\/\/stackoverflow.com\/questions\/5978484\/what-is-the-advantage-of-using-varbinary-over-varchar-here\">http:\/\/stackoverflow.com\/questions\/5978484\/what-is-the-advantage-of-using-varbinary-over-varchar-here<\/a><\/li>\n<li>=<\/li>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/binary-varbinary.html\">https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/binary-varbinary.html<\/a><\/li>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/string-types.html\">https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/string-types.html<\/a><\/li>\n<li>=<\/li>\n<li><a href=\"http:\/\/blog.sina.com.cn\/s\/blog_4de07d5e01010jc4.html\">http:\/\/blog.sina.com.cn\/s\/blog_4de07d5e01010jc4.html<\/a><\/li>\n<li>=<\/li>\n<li><a href=\"http:\/\/kekoav.com\/posts\/uuid-primary-key-mysql\">http:\/\/kekoav.com\/posts\/uuid-primary-key-mysql<\/a><\/li>\n<li><a href=\"http:\/\/www.percona.com\/blog\/2012\/08\/16\/mysql-indexing-best-practices-webinar-questions-followup\/\">http:\/\/www.percona.com\/blog\/2012\/08\/16\/mysql-indexing-best-practices-webinar-questions-followup\/<\/a><\/li>\n<\/ul>\n<h6>\u53c2\u8003\u56de\u7b54\uff1a<\/h6>\n<p>the binary comparisons will be more efficient, since it won&#8217;t involve all of the code that&#8217;s there to deal with collations.\uff08<strong>\u4e8c\u8fdb\u5236\u7684\u6bd4\u8f83\u6548\u7387\u66f4\u9ad8<\/strong>\uff1bvarbinary\u5728\u6709\u7d22\u5f15\u7684\u60c5\u51b5\u4e0b\u6548\u7387\u8f83varchar\u7c7b\u578b\u7684\u66f4\u9ad8\uff09<\/p>\n<p>have no character set or collation\uff08<strong>\u6ca1\u6709\u5b57\u7b26\u96c6\u9650\u5236<\/strong>\uff09<\/p>\n<p>binary\u4fdd\u5b58\u4e8c\u8fdb\u5236\u5b57\u7b26\u4e32\uff0c\u5b83\u4fdd\u5b58\u7684\u662f\u5b57\u8282\u800c\u4e0d\u662f\u5b57\u7b26\uff0c\u6ca1\u6709\u5b57\u7b26\u96c6\u9650\u5236<\/p>\n<p>binary(8)\u53ef\u4ee5\u4fdd\u5b588\u4e2a\u5b57\u7b26\uff0c\u6bcf\u4e2a\u5b57\u7b26\u53601\u4e2a\u5b57\u8282\uff0c\u5171\u53608\u4e2a\u5b57\u8282<\/p>\n<p>\u8fdb\u884c\u6bd4\u8f83\u65f6\u662f\u6309\u5b57\u8282\u8fdb\u884c\u6bd4\u8f83\uff0c\u800c\u4e0d\u662f\u6309\u5b57\u7b26\uff08char\uff09\uff0c<span style=\"color: #ff0000;\">\u6309\u5b57\u8282\u6bd4\u8f83\u6bd4\u5b57\u7b26\u7b80\u5355\u5feb\u901f<\/span><\/p>\n<p>\u6309\u5b57\u7b26\u6bd4\u8f83\u4e0d\u533a\u5206\u5927\u5c0f\u5199\uff0c\u800cbinary\u5728\u6bd4\u8f83\u65f6<strong>\u533a\u5206\u5927\u5c0f\u5199<\/strong>\uff0c\u7ed3\u5c3e\u4f7f\u7528\\0\u586b\u5145\uff0c\u800c\u4e0d\u662f\u7a7a\u683c<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u5b98\u65b9\u6587\u6863\uff1a MySQL :: MySQL 5.5 Reference Manual :: 11.4.2 The [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14,23,6],"tags":[16,497,498],"class_list":["post-2118","post","type-post","status-publish","format-standard","hentry","category-database","category-knowledgebase-2","category-other","tag-mysql","tag-varbinary","tag-varchar"],"views":4941,"_links":{"self":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/2118","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/comments?post=2118"}],"version-history":[{"count":0,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/2118\/revisions"}],"wp:attachment":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/media?parent=2118"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/categories?post=2118"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/tags?post=2118"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}