Appendix聽A.聽Problems and Common Errors - MySQL 5.1参考手册中文版

瀵艰嚧鎷掔粷璁块棶閿欒��鐨勫師鍥犲緢澶氥�傝�ラ敊璇�甯镐笌杩炴帴鏃舵湇鍔″櫒鍏佽�稿�㈡埛绔�浣跨敤鐨�MySQL璐︽埛鏈夊叧銆傝�峰弬瑙�5.7.8鑺傦紝鈥滄嫆缁濊�块棶閿欒��鐨勫師鍥�鈥�銆傝�峰弬瑙�5.7.2鑺傦紝鈥滄潈闄愮郴缁熷伐浣滃師鐞嗏��銆�

Unix骞冲彴涓婄殑MySQL瀹㈡埛绔�鑳藉�熶互涓ょ�嶄笉鍚岀殑鏂瑰紡杩炴帴鍒�mysqld鏈嶅姟鍣�锛氶�氳繃鏂囦欢绯荤粺涓�鐨勬枃浠讹紙榛樿�や负/tmp/mysql.sock锛変娇鐢�Unix濂楁帴瀛楄繘琛岃繛鎺ワ紝鎴栭�氳繃绔�鍙ｅ彿浣跨��TCP/IP杩涜�岃繛鎺ャ��Unix濂楁帴瀛楁枃浠剁殑杩炴帴閫熷害姣�TCP/IP蹇�锛屼絾浠呰兘鍦ㄤ笌鐩稿悓璁＄畻鏈轰笂鐨勬湇鍔″櫒鐩歌繛鏃朵娇鐢ㄣ�傚�傛灉鏈�鎸囧畾鎸囧畾涓绘満鍚嶆垨鎸囧畾浜嗙壒娈婄殑涓绘満鍚�localhost锛屽皢浣跨敤Unix濂楁帴瀛椼��

濡傛灉MySQL鏈嶅姟鍣ㄨ繍琛屽湪Windows 9x鎴�Me涓婏紝浠呰兘閫氳繃TCP/IP杩涜�岃繛鎺ャ�傚�傛湇鍔″櫒杩愯�屽湪Windows NT銆�2000銆�XP鎴�2003涓婏紝鑰屼笖浣跨敤--enable-named-pipe閫夐」鍚�鍔�锛屽�傛灉鍦ㄨ繍琛屾湇鍔″櫒鐨勬満鍣ㄤ笂杩愯�屽�㈡埛绔�锛屼篃鑳戒娇鐢ㄥ懡鍚嶇�￠亾杩涜�岃繛鎺ャ��榛樿�ゆ儏鍐典笅锛屽懡鍚嶇�￠亾鐨勫悕绉颁负MySQL銆傚�傛灉鍦ㄨ繛鎺ュ��mysqld鏃舵湭缁欏畾涓绘満鍚嶏紝MySQL瀹㈡埛绔�棣栧厛浼氬皾璇曡繛鎺ュ埌鍛藉悕绠￠亾銆傚�傛灉涓嶈兘宸ヤ綔锛屽皢杩炴帴鍒�TCP/IP绔�鍙ｃ�備娇鐢ㄢ��.鈥濅綔涓轰富鏈哄悕锛屽彲鍦�Windows骞冲彴涓婂己鍒朵娇鐢ㄥ懡鍚嶇�￠亾銆�

閿欒��(2002)鈥滄棤娉曡繛鎺ュ埌鈥︹��閫氬父鎰忓懗鐫�鍦ㄧ郴缁熸病鏈夎繍琛岀殑MySQL鏈嶅姟鍣�锛屾垨鍦ㄨ繛鎺ュ埌鏈嶅姟鍣ㄦ椂浣跨敤浜嗕笉姝ｇ‘鐨�Unix濂楁帴瀛楁枃浠跺悕鎴�TCP/IP绔�鍙ｅ彿銆�

棣栧厛妫�鏌ユ湇鍔″櫒涓绘満涓婃槸鍚︽湁鍚嶄负mysqld鐨勮繘绋嬶紙鍦�Unix骞冲彴涓婁娇鐢�ps xa | grep mysqld锛屾垨鍦�Windows骞冲彴涓婁娇鐢ㄤ换鍔＄�＄悊鍣�锛夈�傚�傛灉娌℃湁杩欑被杩涚▼锛屽簲鍚�鍔ㄦ湇鍔″櫒銆傝�峰弬瑙�2.9.2.3鑺傦紝鈥滃惎鍔∕ySQL鏈嶅姟鍣ㄤ互鍙婂叾鏁呴殰璇婃柇鍜屾帓闄も��銆�

濡傛灉mysqld杩涚▼姝ｅ湪杩愯�岋紝鍙�浣跨敤涓嬭堪鍛戒护妫�鏌ャ�傚湪浣犵殑鍏蜂綋璁剧疆涓�锛岀��鍙ｅ彿鎴�Unix濂楁帴瀛楁枃浠跺悕鍙�鑳戒細鏈夋墍涓嶅悓銆�host_ip浠ｈ〃杩愯�屾湇鍔″櫒鐨勬満鍣ㄧ��IP缂栧彿銆�

shell> mysqladmin version

shell> mysqladmin variables

shell> mysqladmin -h `hostname` version variables

shell> mysqladmin -h `hostname` --port=3306 version

shell> mysqladmin -h host_ip version

shell> mysqladmin --protocol=socket --socket=/tmp/mysql.sock version

娉ㄦ剰锛屽簲涓庝富鏈哄悕鍛戒护涓�璧蜂娇鐢ㄢ��`鈥濊�屼笉鏄�鈥溾�欌�濓紝杩欎細浣夸富鏈哄悕杈撳嚭锛堝綋鍓嶄富鏈哄悕锛夎��浠ｅ叆mysqladmin鍛戒护銆傚�傛灉娌℃湁涓绘満鍚嶅懡浠ゆ垨姝ｈ繍琛屽��Windows骞冲彴涓婏紝搴斾互鎵嬪姩鏂瑰紡杈撳叆鏈哄櫒鐨勪富鏈哄悕锛堟棤鈥�`鈥濈�﹀彿锛夛紝鍚庤��-h閫夐」銆�涔熷彲浠ヤ娇鐢�TCP/IP鍗忚��鐢�-h 127.0.0.1杩炴帴鍒版湰鍦颁富鏈恒��

涓嬮潰缁欏嚭浜嗕竴浜涒�滄棤娉曡繛鎺ュ埌鏈�鍦�MySQL鏈嶅姟鍣ㄢ�濋敊璇�鐨勫彲鑳藉師鍥狅��

1.    Mysqld鏈�杩愯�屻�傝�锋��鏌ユ搷浣滅郴缁熺殑杩涚▼鍒楄〃浠ョ‘淇�mysqld杩涚▼姝ｅ湪杩愯�屻��

2.    浣犳�ｅ湪鍏锋湁寰堝��TCP/IP杩炴帴鐨�Windows骞冲彴涓婅繍琛�MySQL鏈嶅姟鍣ㄣ�傚�傛灉浣犵殑瀹㈡埛绔�缁忓父鍑虹幇閿欒��锛岃�峰弬瑙�A.2.2.1鑺傦紝鈥滃湪Windows涓婁笌MySQL鏈嶅姟鍣ㄧ殑杩炴帴澶辫触鈥�锛屼互鎵惧嚭瑙勯伩鏂规硶銆�

3.    浣犳�ｅ湪浣跨��MIT-pthreads鐨勭郴缁熶笂杩愯�屻�傚�傛灉浣犳�ｅ湪杩愯�屼笉鍏锋湁鍥烘湁绾跨▼鐨勬搷浣滅郴缁燂��mysqld灏嗕娇鐢�MIT-pthreads杞�浠跺寘銆傝�峰弬瑙�2.1.1鑺傦紝鈥淢ySQL鏀�鎸佺殑鎿嶄綔绯荤粺鈥�銆備絾鏄�锛屽苟闈炴墍鏈夌殑MIT-pthreads鐗堟湰鍧囨敮鎸�Unix濂楁帴瀛楁枃浠躲�傚湪涓嶆敮鎸佸�楁帴瀛楁枃浠剁殑绯荤粺涓婏紝杩炴帴鍒版湇鍔″櫒鏃讹紝蹇呴』鏄庣‘鎸囧畾涓绘満鍚嶃�傝�蜂娇鐢ㄤ笅杩板懡浠ゆ潵妫�鏌ユ槸鍚﹁繛鎺ュ埌浜嗘湇鍔″櫒锛�

4.           shell> mysqladmin -h `hostname` version

5.    鏌愪汉绉诲姩浜�mysqld浣跨敤鐨�Unix濂楁帴瀛楁枃浠讹紙榛樿�や��/tmp/mysql.sock锛夈�備緥濡傦紝浣犲彲鑳芥墽琛屼簡灏嗘棫鏂囦欢浠�/tmp鐩�褰曞垹闄ょ��cron浠诲姟銆備綘鎬昏兘鎵ц��mysqladmin version鏉ユ��鏌�mysqladmin璇曞浘浣跨敤鐨�Unix濂楁帴瀛楁枃浠舵槸鍚︾殑纭�瀛樺湪銆傚湪璇ユ儏鍐典笅锛屾洿姝ｆ柟寮忔槸鏇存敼cron浠诲姟锛屼笉鍒犻櫎mysql.sock鏂囦欢锛屾垨灏嗗�楁帴瀛楁枃浠剁疆浜庡叾浠栧湴鏂广��璇峰弬瑙�A.4.5鑺傦紝鈥滃�備綍淇濇姢鎴栨洿鏀筂ySQL濂楁帴瀛楁枃浠�/tmp/mysql.sock鈥�銆�

6.    浣犱娇鐢�--socket=/path/to/socket閫夐」鍚�鍔ㄤ��mysqld鏈嶅姟鍣�锛屽綋蹇樿�板皢濂楁帴瀛楁枃浠剁殑鏂板悕绉伴�氱煡瀹㈡埛绔�绋嬪��銆傚�傛灉鏇存敼浜嗗叧浜庢湇鍔″櫒鐨勫�楁帴瀛楄矾寰勶紝涔熷繀椤婚�氱煡MySQL瀹㈡埛绔�銆傚彲鍦ㄨ繍琛屽�㈡埛绔�绋嬪簭鏃朵娇鐢ㄧ浉鍚岀殑鈥攕ocket閫夐」鏉ュ畬鎴愯�ヤ换鍔°�傛�ゅ�栵紝浣犺繕搴旂‘淇濆�㈡埛绔�鍏锋湁璁块棶鏂囦欢mysql.sock鐨勬潈闄愩�傝�佹兂鎵惧嚭濂楁帴瀛楁枃浠剁殑浣嶇疆锛屽彲浣跨敤锛�

7.           shell> netstat -ln | grep mysql

璇峰弬瑙�A.4.5鑺傦紝鈥滃�備綍淇濇姢鎴栨洿鏀筂ySQL濂楁帴瀛楁枃浠�/tmp/mysql.sock鈥�銆�

8.    浣犳�ｅ湪浣跨��Linux鑰屼笖1涓�鏈嶅姟鍣ㄧ嚎绋嬪凡姝讳骸锛堝唴鏍稿凡娓呴櫎锛夈�傚湪姝ゆ儏鍐典笅锛屽湪閲嶅惎MySQL鏈嶅姟鍣ㄤ箣鍓嶏紝蹇呴』鏉�姝诲叾浠�mysqld绾跨▼锛堜緥濡傦紝浣跨敤kill鎴�mysql_zap鑴氭湰锛夈��璇峰弬瑙�A.4.2鑺傦紝鈥滃�傛灉MySQL渚濈劧宕╂簝锛屽簲浣滀簺浠�涔堚��銆�

9.    鏈嶅姟鍣ㄦ垨瀹㈡埛绔�绋嬪簭涓嶅叿鏈夎�块棶鍖呭惈Unix濂楁帴瀛楁枃浠剁殑鐩�褰曟垨濂楁帴瀛楁枃浠舵湰韬�鐨勬伆褰撴潈闄愩�傚湪璇ユ儏鍐典笅锛屽繀椤绘洿鏀圭洰褰曟垨濂楁帴瀛楁枃浠剁殑璁块棶鏉冮檺锛屼互渚挎湇鍔″櫒鎴栧�㈡埛绔�绋嬪簭鑳藉�熻�块棶瀹冧滑锛屾垨鐢�鈥搒ocket閫夐」閲嶅惎mysqld锛屽湪璇ラ�夐」涓�鎸囧畾鏈嶅姟鍣ㄨ兘鍒涘缓銆佽�屼笖瀹㈡埛绔�鍙�璁块棶鐨勭洰褰曚笅鐨勫�楁帴瀛楁枃浠跺悕銆�

濡傛灉閬囧埌閿欒��娑堟伅鈥滄棤娉曡繛鎺ュ埌some_host涓婄殑MySQL鏈嶅姟鍣�鈥濓紝鍙�灏濊瘯閲囧彇涓嬭堪姝ラ�や互鎵惧嚭闂�棰樻墍鍦�锛�

路         鎵ц�屸��telnet some_host 3306鈥�骞舵寜涓ゆ�″洖杞﹂敭锛屾��鏌ユ湇鍔″櫒鏄�鍚﹁繍琛屽湪璇ヤ富鏈轰笂锛�3306鏄�榛樿�ょ殑MySQL绔�鍙ｅ彿銆傚�傛灉浣犵殑鏈嶅姟鍣ㄦ�ｅ湪鐩戝惉涓嶅悓鐨勭��鍙ｏ紝璇锋洿鏀硅�ュ�硷級銆傚�傛灉鏈�1涓�MySQL鏈嶅姟鍣ㄦ�ｅ湪杩愯�屽苟鐩戝惉璇ョ��鍙ｏ紝浣犲簲鏀跺埌鍖呭惈鏈嶅姟鍣ㄧ増鏈�鍙风殑鍥炲簲銆傚�傛灉閬囧埌閿欒��锛屽�傗��telnet:鏃犳硶杩炴帴鍒拌繙绋嬩富鏈�:鎷掔粷杩炴帴鈥�锛岃〃绀哄湪璇ュ畾绔�鍙ｄ笂娌℃湁杩愯�岀殑鏈嶅姟鍣ㄣ��

路         濡傛灉鏈嶅姟鍣ㄦ�ｈ繍琛屽湪鏈�鍦颁富鏈轰笂锛岃�蜂娇鐢�Unix濂楁帴瀛楁枃浠讹紝骞朵娇鐢�mysqladmin -h localhost variables杩涜�岃繛鎺ャ�傞獙璇佹湇鍔″櫒鐩戝惉鐨�TCP/IP绔�鍙ｅ彿锛堝畠鏄�port鍙橀噺鐨勫�硷級銆�

路         纭�淇濅綘鐨�mysqld鏈嶅姟鍣ㄦ湭鐢�--skip-networking閫夐」鍚�鍔ㄣ��濡傛灉浣跨敤浜嗚�ラ�夐」锛屽皢鏃犳硶浣跨敤TCP/IP杩炴帴鍒板畠銆�

路         妫�鏌ュ苟纭�璁や笉瀛樺湪灞忚斀浜嗗��MySQL璁块棶鐨勯槻鐏�澧欍�傞渶瑕侀厤缃�璇稿��ZoneAlarm鍜�Windows XP涓�浜洪槻鐏�澧欑瓑搴旂敤绋嬪簭锛屼互鍏佽�稿��MySQL鏈嶅姟鍣ㄧ殑澶栭儴璁块棶銆�

路                HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters

路                Value Name: MaxUserPort

路                Data Type: REG_DWORD

路                Value: 65534

路                Value Name: TcpTimedWaitDelay

路                Data Type: REG_DWORD

路                Value: 30

MySQL 5.1閲囩敤浜嗗熀浜庡瘑鐮佹贩缂栫畻娉曠殑閴村畾鍗忚��锛屽畠涓庢棭鏈熷�㈡埛绔�锛�4.1涔嬪墠锛変娇鐢ㄧ殑鍗忚��涓嶅吋瀹广�傚�傛灉浣犲皢鏈嶅姟鍣ㄥ崌绾у��4.1涔嬩笂锛岀敤鏃╂湡鐨勫�㈡埛绔�杩涜�岃繛鎺ュ彲鑳藉け璐ワ紝骞剁粰鍑轰笅杩版秷鎭�锛�

shell> mysql

瀹㈡埛绔�涓嶆敮鎸佹湇鍔″櫒璇锋眰鐨勯壌瀹氬崗璁�锛氳�疯�冭檻鍗囩骇MySQL瀹㈡埛绔�銆�

瑕佹兂瑙ｅ喅璇ラ棶棰橈紝搴斾娇鐢ㄤ笅杩版柟娉曚箣涓�锛�

路         鍗囩骇鎵�鏈夌殑瀹㈡埛绔�绋嬪簭锛屼互浣跨��4.1.1鎴栨洿鏂扮殑瀹㈡埛绔�搴撱��

路         鐢�4.1鐗堜箣鍓嶇殑瀹㈡埛绔�杩炴帴鍒版湇鍔″櫒鏃讹紝璇蜂娇鐢ㄤ粛鍏锋��4.1鐗堜箣鍓嶉�庢牸瀵嗙爜鐨勮处鎴枫��

路         瀵逛簬闇�瑕佷娇鐢�4.1鐗堜箣鍓嶇殑瀹㈡埛绔�鐨勬瘡浣嶇敤鎴凤紝灏嗗瘑鐮佹仮澶嶄��4.1鐗堜箣鍓嶇殑椋庢牸銆傚彲浠ヤ娇鐢�SET PASSWORD璇�鍙ュ��OLD_PASSWORD()鍑芥暟瀹屾垚璇ヤ换鍔★細

路                mysql> SET PASSWORD FOR

路                    -> 'some_user'@'some_host' = OLD_PASSWORD('newpwd');

涔熷彲浠ヤ娇鐢�UPDATE鍜�FLUSH PRIVILEGES锛�

mysql> UPDATE mysql.user SET Password = OLD_PASSWORD('newpwd')

    -> WHERE Host = 'some_host' AND User = 'some_user';

mysql> FLUSH PRIVILEGES;

鐢ㄤ綘鎵撶畻浣跨敤鐨勫瘑鐮佹浛鎹㈠墠渚嬩腑鐨勨��newpwd鈥濄��MySQL涓嶈兘鍛婅瘔浣犲師鏉ョ殑瀵嗙爜鏄�浠�涔堬紝鍥犳�わ紝浣犻渶瑕侀�夋嫨鏂扮殑瀵嗙爜銆�

路         閫氱煡鏈嶅姟鍣ㄤ娇鐢ㄦ棫鐨勫瘑鐮佹贩缂栫畻娉曪細

1.    浣跨敤鈥�--old-passwords鈥濋�夐」鍚�鍔�mysqld銆�

2.    瀵逛簬宸插皢瀵嗙爜鏇存柊涓鸿緝闀�4.1鏍煎紡鐨勬瘡涓�璐︽埛锛屼负鍏舵寚瀹氬叿鏈夋棫鏍煎紡鐨勫瘑鐮併�傚彲浠ヤ娇鐢ㄤ笅杩版煡璇㈢‘瀹氳繖浜涜处鎴凤細

3.                 mysql> SELECT Host, User, Password FROM mysql.user

4.                     -> WHERE LENGTH(Password) > 16;

瀵逛簬鏌ヨ�㈡樉绀虹殑姣忎釜璐︽埛璁板綍锛岃�蜂娇鐢�Host鍜�User鍊硷紝骞朵娇鐢�OLD_PASSWORD()鍑芥暟浠ュ強SET PASSWORD鎴�UPDATE涔嬩竴鎸囧畾瀵嗙爜锛屽�傚墠闈㈡墍浠嬬粛鐨勯偅鏍枫��

娉ㄩ噴锛�鍦ㄦ棭鏈熺殑PHP鐗堟湰涓�锛�mysql鎵╁睍涓嶆敮鎸�MySQL 4.1.1鍜屾洿楂樼増涓�鐨勯壌瀹氬崗璁�銆傛棤璁轰娇鐢ㄧ殑PHP鐗堟湰鏄�浠�涔堬紝瀹冨潎鏄�姝ｇ‘鐨勩�傚�傛灉浣犳墦绠椾��MySQL 4.1鎴栨洿楂樼増鏈�涓�璧蜂娇鐢�mysql鎵╁睍锛岄渶瑕佷娇鐢ㄥ墠闈�浠嬬粛鐨勯�夐」涔嬩竴锛岄厤缃�MySQL锛屼互渚夸笌杈冩棭鐨勫�㈡埛绔�涓�璧蜂娇鐢ㄣ��mysqli鎵╁睍锛堟敮鎸�鈥�鏀硅繘鐨�MySQL鈥�锛屽湪PHP 5涓�澧炲姞锛変��MySQL 4.1鍜屾洿楂樼増鏈�涓�浣跨敤鐨勬敼杩涚殑瀵嗙爜娣风紪绠楁硶鍏煎�癸紝涓嶉渶瑕佸��MySQL杩涜�岀壒娈婇厤缃�灏辫兘浣跨敤璇�MySQL瀹㈡埛绔�搴撱�傚叧浜�mysqli鎵╁睍鐨勬洿澶氫俊鎭�锛岃�峰弬瑙�http://php.net/mysqli銆�

鍏充簬瀵嗙爜娣风紪鍜岄壌瀹氬姛鑳界殑棰濆�栬儗鏅�鐭ヨ瘑锛岃�峰弬瑙�5.7.9鑺傦紝鈥淢ySQL 4.1涓�鐨勫瘑鐮佸搱甯屽�勭悊鈥�銆�

shell> mysql -u user_name -p

Enter password:

濡傛灉閬囧埌涓嬭堪閿欒��锛岃〃绀�mysqld宸叉敹鍒版潵鏉ヨ嚜涓绘満鈥�host_name鈥濈殑寰堝�氳繛鎺ヨ�锋眰锛屼絾璇ヤ富鏈哄嵈鍦ㄤ腑閫斾腑鏂�銆�

鐢变簬鍑虹幇寰堝�氳繛鎺ラ敊璇�锛屼富鏈�'host_name'琚�灞忚斀銆�

鍙�浣跨��'mysqladmin flush-hosts'瑙ｉ櫎灞忚斀銆�

鍏佽�哥殑涓�鏂�杩炴帴璇锋眰鐨勬暟鐩�鐢�max_connect_errors绯荤粺鍙橀噺鐨勫�煎喅瀹氥��褰撹秴鍑�max_connect_errors瑙勫畾鐨勮繛鎺ヨ�锋眰鏃讹��mysqld灏嗚�や负鏌愬�勫嚭閿欙紙渚嬪�傦紝鏌愪汉姝ｈ瘯鍥炬彃鍏ワ級锛屽苟灞忚斀涓绘満鐨勮繘涓�姝ヨ繛鎺ヨ�锋眰锛岀洿鑷虫墽琛屼��mysqladmin flush-hosts鍛戒护锛屾垨鍙戝嚭浜�FLUSH HOSTS璇�鍙ヤ负姝�銆傝�峰弬瑙�5.3.3鑺傦紝鈥滄湇鍔″櫒绯荤粺鍙橀噺鈥�銆�

鍦ㄩ粯璁ゆ儏鍐典笅锛�mysqld浼氬湪10娆¤繛鎺ラ敊璇�鍚庡睆钄戒富鏈恒�備綘鍙�浠ラ�氳繃涓嬭堪鏂瑰紡鍚�鍔ㄦ湇鍔″櫒鏉ヨ皟鏁磋�ュ�硷細

shell> mysqld_safe --max_connect_errors=10000 &

濡傛灉鍦ㄧ粰瀹氫富鏈轰笂閬囧埌璇ラ敊璇�锛岄�栧厛搴旀牳瀹炶�ヤ富鏈虹��TCP/IP杩炴帴鏄�鍚︽�ｇ‘銆傚�傛灉瀛樺湪缃戠粶闂�棰橈紝澧炲姞max_connect_errors鍙橀噺鐨勫�间笉浼氭湁浠讳綍濂藉�勩��

褰撲綘璇曞浘杩炴帴鍒�mysqld鏈嶅姟鍣ㄦ椂閬囧埌鈥滆繃澶氳繛鎺モ�濋敊璇�锛岃繖琛ㄧず鎵�鏈夊彲鐢ㄧ殑杩炴帴鍧囧凡琚�鍏朵粬瀹㈡埛绔�浣跨敤銆�

鍏佽�哥殑杩炴帴鏁扮��max_connections绯荤粺鍙橀噺鎺у埗銆�榛樿�ゅ�间负100銆傚�傛灉闇�瑕佹敮鎸佹洿澶氱殑杩炴帴锛屽簲浣跨敤璇ュ彉閲忕殑杈冨ぇ鍊奸噸鍚�mysqld銆�

mysqld瀹為檯涓婂厑璁�max_connections+1涓�瀹㈡埛绔�杩涜�岃繛鎺ャ��棰濆�栫殑杩炴帴淇濈暀缁欏叿鏈�SUPER鏉冮檺鐨勮处鎴枫�傞�氳繃涓虹郴缁熺�＄悊鍛樿�屼笉鏄�鏅�閫氱敤鎴锋巿浜�SUPER鏉冮檺锛堟櫘閫氱敤鎴蜂笉搴斿叿鏈夎�ユ潈闄愶級锛岀郴缁熺�＄悊鍛樿兘澶熻繛鎺ュ埌鏈嶅姟鍣�锛�骞朵娇鐢�SHOW PROCESSLIST鏉ヨ瘖鏂�闂�棰橈紝鍗充娇宸茶繛鎺ョ殑鏃犵壒鏉冨�㈡埛绔�鏁板凡杈惧埌鏈�澶у�间篃鍚屾牱銆�璇峰弬瑙�13.5.4.16鑺傦紝鈥淪HOW PROCESSLIST璇�娉曗��銆�

MySQL鑳芥敮鎸佺殑鏈�澶ц繛鎺ユ暟鍙栧喅浜庣粰瀹氬钩鍙颁笂绾跨▼搴撶殑璐ㄩ噺銆�Linux鎴�Solaris搴旇兘鏀�鎸�500-1000涓�骞跺彂杩炴帴锛屽叿浣撴儏鍐靛彇鍐充��RAM瀹归噺锛屼互鍙婂�㈡埛绔�姝ｅ湪浣滀粈涔堛��MySQL AB鎻愪緵鐨勯潤鎬�Linux搴撹兘鏀�鎸侀珮杈�4000涓�杩炴帴銆�

濡傛灉浣跨敤mysql瀹㈡埛绔�绋嬪簭鍙戝嚭浜嗘煡璇�锛屽苟鏀跺埌涓嬭堪閿欒��涔嬩竴锛屽垯琛ㄧずmysql娌℃湁瓒冲�熷唴瀛樻潵淇濆瓨鍏ㄩ儴鏌ヨ�㈢粨鏋滐細

mysql: Out of memory at line 42, 'malloc.c'

mysql: needed 8136 byte (8k), memory in use: 12481367 bytes (12189k)

閿欒��2008: MySQL client ran out of memory

瑕佹兂鏇存�ｈ�ラ棶棰橈紝棣栧厛搴旀��鏌ユ煡璇㈡槸鍚︽�ｇ‘銆傝繑鍥炶繖涔堝�氳�屾槸鍚﹀悎鐞嗭紵濡傛灉涓嶅悎鐞嗭紝鏇存�ｆ煡璇㈠苟鍐嶆�″皾璇曘�傚惁鍒欙紝搴斾娇鐢ㄢ��--quick鈥濋�夐」璋冪敤mysql銆傝繖鏍凤紝灏嗕娇鐢�mysql_use_result() C API鍑芥暟鏉ユ��绱㈢粨鏋滈泦锛岃繖绫诲嚱鏁拌兘澶熼檷浣庡�㈡埛绔�涓婄殑璐熻浇锛堜絾浼氬姞閲嶆湇鍔″櫒涓婄殑璐熻浇锛夈��

鍦ㄦ湰鑺備腑锛岃繕浠嬬粛浜嗗嚭鐜版煡璇㈤敊璇�鏈熼棿锛屼笌涓㈠け浜嗘湇鍔″櫒杩炴帴鏈夊叧鐨勪簨瀹溿��

MySQL鏈嶅姟鍣ㄤ笉鍙�鐢ㄩ敊璇�鐨勬渶甯歌�佸師鍥犳槸鏈嶅姟鍣ㄨ秴鏃朵互鍙婅繛鎺ュ凡鍏抽棴銆傚湪璇ユ儏鍐典笅锛岄�氬父鑳借�佸埌涓嬭堪閿欒��浠ｇ爜涔嬩竴锛堝叿浣撶殑閿欒��浠ｇ爜涓庢搷浣滅郴缁熸湁鍏筹級锛�

鍦ㄩ粯璁ゆ儏鍐典笅锛屽�傛灉鏈�鍙戠敓浠讳綍浜嬶紝8灏忔椂鍚庢湇鍔″櫒灏嗗叧闂�杩炴帴銆備篃鍙�浠ュ湪鍚�鍔�mysqld鏃讹紝閫氳繃璁剧疆wait_timeout鍙橀噺鏇存敼鏃堕棿闄愬埗銆傝�峰弬瑙�5.3.3鑺傦紝鈥滄湇鍔″櫒绯荤粺鍙橀噺鈥�.

濡傛灉鏈�1涓�鑴氭湰锛屼綘浠呴渶瑕佸啀娆″彂鍑烘煡璇�锛岃�╁�㈡埛绔�鍐嶆�¤繘琛岃嚜鍔ㄨ繛鎺ュ嵆鍙�銆傚叾涓�锛屽亣瀹氬湪瀹㈡埛绔�涓�鍚�鐢ㄤ簡鑷�鍔ㄥ啀杩炴帴鍔熻兘锛堝�逛簬mysql鍛戒护琛屽�㈡埛绔�锛岃繖鏄�榛樿�よ�剧疆锛夈��

MySQL鏈嶅姟鍣ㄤ笉鍙�鐢ㄩ敊璇�鐨勪竴浜涘叾浠栧父瑙佸師鍥犲�備笅锛�

路         浣狅紙鎴�db绯荤粺绠＄悊鍛橈級浣跨敤KILL璇�鍙ユ��mysqladmin kill鍛戒护鏉�姝讳簡姝ｅ湪杩愯�岀殑绾跨▼銆�

路         浣犺瘯鍥惧湪鍏抽棴浜嗕笌鏈嶅姟鍣ㄧ殑杩炴帴鍚庤繍琛屾煡璇��傝繖琛ㄦ槑搴旀洿姝ｅ簲鐢ㄧ▼搴忎腑鐨勯�昏緫閿欒��銆�

路         浣犲湪瀹㈡埛绔�涓�渚ч亣鍒�TCP/IP杩炴帴瓒呮椂閿欒��銆傚�傛灉浣犱娇鐢ㄤ簡鍛戒护锛�mysql_options(..., MYSQL_OPT_READ_TIMEOUT,...)鎴�mysql_options(..., MYSQL_OPT_WRITE_TIMEOUT,...)锛屽氨鍙�鑳藉嚭鐜拌�ラ棶棰樸��鍦ㄨ�ユ儏鍐典笅锛屽�炲姞瓒呮椂鍊煎彲鑳芥湁鍔╀簬闂�棰樼殑瑙ｅ喅銆�

路         浣犲湪鏈嶅姟鍣ㄧ��閬囧埌瓒呮椂閿欒��锛岃�屼笖绂佹��浜嗗�㈡埛绔�涓�鐨勮嚜鍔ㄥ啀杩炴帴鍔熻兘锛�MYSQL缁撴瀯涓�鐨勫啀杩炴帴鏍囧織绛変��0锛夈��

路         浣犳�ｅ湪浣跨��Windows瀹㈡埛绔�锛岃�屼笖鍦ㄥ彂鍑哄懡浠や箣鍓嶆湇鍔″櫒鎾ら攢浜嗚繛鎺ワ紙鎴栬�告槸鍥犱负宸茶秴杩�wait_timeout锛�銆�

鍦�Windows骞冲彴涓婂嚭鐜伴棶棰樼殑鍘熷洜锛屽湪鏌愪簺鎯呭喌涓嬶紝灏�TCP/IP杩炴帴鍐欏叆鏈嶅姟鍣ㄦ椂锛�MySQL鏈�鏀跺埌鏉ヨ嚜鎿嶄綔绯荤粺鐨勯敊璇�锛屼絾褰撹瘯鍥句粠杩炴帴璇诲彇绛旀�堟椂鍑虹幇閿欒��銆�

鍦ㄨ�ユ儏鍐典笅锛屽嵆浣�MYSQL缁撴瀯涓�鐨勫啀杩炴帴鏍囧織绛変��1锛�MySQL涔熶笉浼氭墽琛岃嚜鍔ㄥ啀杩炴帴骞跺啀娆″彂鍑烘煡璇�锛岃繖鏄�鍥犱负瀹冧笉鐭ラ亾鏈嶅姟鍣ㄦ槸鍚︽敹鍒板師濮嬫煡璇���

瀵规�ょ殑瑙ｅ喅鏂瑰紡鏄�锛氬�傛灉鑷�涓婁竴娆℃煡璇�浠ユ潵缁忚繃浜嗚緝闀挎椂闂达紝鍦ㄨ繛鎺ヤ笂鎵ц��mysql_ping锛堟�ｆ��MyODBC鎵�浣滅殑锛夛紱鎴栧湪mysqld鏈嶅姟鍣ㄤ笂灏�wait_timeout璁剧疆寰楀緢楂橈紝浣夸箣瀹為檯涓婁笉瀛樺湪瓒呮椂銆�

路         濡傛灉浣犲悜鏈嶅姟鍣ㄥ彂鍑轰簡涓嶆�ｇ‘鎴栬繃澶х殑鏌ヨ��锛屼篃浼氶亣鍒拌繖绫婚棶棰樸�傚�傛��mysqld鏀跺埌杩囧ぇ鎴栨棤搴忕殑淇℃伅鍖咃紝瀹冧細璁や负瀹㈡埛绔�鍑洪敊锛屽苟鍏抽棴杩炴帴銆傚�傛灉闇�瑕佹墽琛岃緝澶х殑鏌ヨ��锛堜緥濡傦紝姝ｅ湪澶勭悊澶х殑BLOB鍒楋級锛�鍙�閫氳繃璁剧疆鏈嶅姟鍣ㄧ殑max_allowed_packet鍙橀噺锛屽�炲姞鏌ヨ�㈤檺鍒跺�硷紝璇ュ彉閲忕殑榛樿�ゅ�间负1MB銆傛垨璁革紝浣犺繕闇�澧炲姞瀹㈡埛绔�涓婄殑鏈�澶т俊鎭�鍖呭ぇ灏忋�傚叧浜庤�剧疆淇℃伅鍖呭ぇ灏忕殑鏇村�氫俊鎭�锛岃�峰弬瑙�A.2.9鑺傦紝鈥滀俊鎭�鍖呰繃澶р��銆�

路         濡傛灉浣犵殑瀹㈡埛绔�浣庝��4.0.8鑰屼笖浣犵殑鏈嶅姟鍣ㄩ珮浜�4.0.8锛屽綋浣犳帴鏀�16MB鎴栨洿澶х殑淇℃伅鍖呮椂锛屽彲鑳戒細涓㈠け杩炴帴銆�

路         濡傛灉MySQL鏄�鐢ㄢ��--skip-networking鈥濋�夐」鍚�鍔ㄧ殑锛屼篃浼氳�佸埌MySQL鏈嶅姟鍣ㄤ笉鍙�鐢ㄩ敊璇�銆�

路         浣犻亣鍒颁簡鎵ц�屾煡璇㈡椂鏈嶅姟鍣ㄥ畷鏈虹殑缂洪櫡銆�

閫氳繃鎵ц��mysqladmin version骞舵��鏌ユ湇鍔″櫒鐨勬�ｅ父宸ヤ綔鏃堕棿锛屽彲妫�鏌ユ湇鍔″櫒鏄�鍚﹀畷鏈哄苟閲嶅惎銆傚�傛灉瀹㈡埛绔�杩炴帴鏄�鍥�mysqld宕╂簝鍜岄噸鍚�鑰屾柇寮�鐨勶紝搴斿皢閲嶇偣鏀惧湪鏌ユ壘宕╂簝浣犳柟闈��傞�栧厛搴斿啀娆℃��鏌ュ彂鍑虹殑鏌ヨ�㈡槸鍚﹀啀娆℃潃姝讳簡鏈嶅姟鍣ㄣ�傝�峰弬瑙�A.4.2鑺傦紝鈥滃�傛灉MySQL渚濈劧宕╂簝锛屽簲浣滀簺浠�涔堚��銆�

鐢ㄢ��--log-warnings=2鈥�閫夐」鍚�鍔�mysqld锛屽彲鑾峰緱鍏充簬杩炴帴鐨勬洿澶氫俊鎭�銆傝繖鏍凤紝灏辫兘灏嗘煇浜涙柇寮�杩炴帴閿欒��璁板綍鍒�hostname.err鏂囦欢涓�銆傝�峰弬瑙�5.11.1鑺傦紝鈥滈敊璇�鏃ュ織鈥�銆�

濡傛灉浣犳墦绠楀垱寤轰笌璇ラ棶棰樻湁鍏崇殑缂洪櫡鎶ュ憡锛屽姟蹇呭寘鍚�涓嬭堪淇℃伅锛�

1.    鎸囨槑MySQL鏈嶅姟鍣ㄦ槸鍚﹀畷鏈恒�傞�氳繃鏈嶅姟鍣ㄩ敊璇�鏃ュ織鍙�鍙戠幇杩欐柟闈㈢殑淇℃伅銆傝�峰弬瑙�A.4.2鑺傦紝鈥滃�傛灉MySQL渚濈劧宕╂簝锛屽簲浣滀簺浠�涔堚��銆�

2.    濡傛灉鐗瑰畾鏌ヨ�㈡潃姝讳簡mysqld锛岃�屼笖鍦ㄨ繍琛屾煡璇㈠墠鐢�CHECK TABLE妫�鏌ヤ簡娑夊強鐨勮〃锛屼綘鏄�鍚﹁兘鎻愪緵鍙�閲嶅�嶇殑娴嬭瘯鑼冧緥锛�璇峰弬瑙�E.1.6鑺傦紝鈥滃�傛灉鍑虹幇琛ㄥ穿婧冿紝璇风敓鎴愭祴璇曟�堜緥鈥�銆�

3.    鍦�MySQL鏈嶅姟鍣ㄤ腑锛岀郴缁熷彉閲�wait_timeout鐨勫�兼槸浠�涔堬紵锛�mysqladmin variables缁欏嚭浜嗚�ュ彉閲忕殑鍊硷級銆�

4.    浣犳槸鍚﹀皾璇曚娇鐢ㄢ��--log鈥濋�夐」鏉ヨ繍琛�mysqld锛屼互纭�瀹氭槸鍚﹀湪鏃ュ織涓�鍑虹幇闂�棰橈��

鍙﹁�峰弬瑙�A.2.10鑺傦紝鈥滈�氫俊閿欒��鍜屽け鏁堣繛鎺モ��銆�

璇峰弬瑙�1.7.1.2鑺傦紝鈥滆�锋暀闂�棰樻垨閫氭姤缂洪櫡鈥�銆�

閫氫俊淇℃伅鍖呮槸鍙戦�佽嚦MySQL鏈嶅姟鍣ㄧ殑鍗曚釜SQL璇�鍙ワ紝鎴栧彂閫佽嚦瀹㈡埛绔�鐨勫崟涓�琛屻��

鍦�MySQL 5.1鏈嶅姟鍣ㄥ拰瀹㈡埛绔�涔嬮棿鏈�澶ц兘鍙戦�佺殑鍙�鑳戒俊鎭�鍖呬负1GB銆�

褰�MySQL瀹㈡埛绔�鎴�mysqld鏈嶅姟鍣ㄦ敹鍒板ぇ浜�max_allowed_packet瀛楄妭鐨勪俊鎭�鍖呮椂锛屽皢鍙戝��鈥�淇℃伅鍖呰繃澶�鈥�閿欒��锛屽苟鍏抽棴杩炴帴銆傚�逛簬鏌愪簺瀹㈡埛绔�锛屽�傛灉閫氫俊淇℃伅鍖呰繃澶э紝鍦ㄦ墽琛屾煡璇㈡湡闂达紝浜嗚兘鍥為亣鍒�鈥�涓㈠け涓�MySQL鏈嶅姟鍣ㄧ殑杩炴帴鈥�閿欒��銆�

瀹㈡埛绔�鍜屾湇鍔″櫒鍧囨湁鑷�宸辩殑max_allowed_packet鍙橀噺锛�鍥犳�わ紝濡備綘鎵撶畻澶勭悊澶х殑淇℃伅鍖咃紝蹇呴』澧炲姞瀹㈡埛绔�鍜屾湇鍔″櫒涓婄殑璇ュ彉閲忋��

濡傛灉浣犳�ｅ湪浣跨��mysql瀹㈡埛绔�绋嬪簭锛屽��max_allowed_packet鍙橀噺鐨勯粯璁ゅ�间负16MB銆傝�佹兂璁剧疆杈冨ぇ鐨勫�硷紝鍙�鐢ㄤ笅杩版柟寮忓惎鍔�mysql锛�

mysql> mysql --max_allowed_packet=32M

瀹冨皢淇℃伅鍖呯殑澶у皬璁剧疆涓�32MB銆�

鏈嶅姟鍣ㄧ殑榛樿��max_allowed_packet鍊间负1MB銆傚�傛灉鏈嶅姟鍣ㄩ渶瑕佸�勭悊澶х殑鏌ヨ��锛屽彲澧炲姞璇ュ�硷紙渚嬪�傦紝濡傛灉鍑嗗�囧�勭悊澶х��BLOB鍒楋級銆備緥濡傦紝瑕佹兂灏嗚�ヨ�剧疆涓�16MB锛屽彲閲囩敤涓嬭堪鏂瑰紡鍚�鍔ㄦ湇鍔″櫒锛�

mysql> mysqld --max_allowed_packet=16M

涔熻兘浣跨敤閫夐」鏂囦欢鏉ヨ�剧��max_allowed_packet銆�瑕佹兂灏嗘湇鍔″櫒鐨勮�ュ彉閲忚�剧疆涓�16MB锛屽彲鍦ㄩ�夐」鏂囦欢涓�澧炲姞涓嬭�屽唴瀹癸細

[mysqld]

max_allowed_packet=16M

澧炲姞璇ュ彉閲忕殑鍊煎崄鍒嗗畨鍏�锛岃繖鏄�鍥犱负浠呭綋闇�瑕佹椂鎵嶄細鍒嗛厤棰濆�栧唴瀛樸�備緥濡傦紝浠呭綋浣犲彂鍑洪暱鏌ヨ�㈡��mysqld蹇呴』杩斿洖澶х殑缁撴灉琛屾椂mysqld鎵嶄細鍒嗛厤鏇村�氬唴瀛樸�傝�ュ彉閲忎箣鎵�浠ュ彇杈冨皬榛樿�ゅ�兼槸涓�绉嶉�勯槻鎺�鏂斤紝浠ユ崟鑾峰�㈡埛绔�鍜屾湇鍔″櫒涔嬮棿鐨勯敊璇�淇℃伅鍖咃紝骞剁‘淇濅笉浼氬洜鍋剁劧浣跨敤澶х殑淇℃伅鍖呰�屽�艰嚧鍐呭瓨婧㈠嚭銆�

濡傛灉浣犳�ｆ槸鐢ㄥぇ鐨�BLOB鍊硷紝鑰屼笖鏈�涓�mysqld鎺堜簣涓哄�勭悊鏌ヨ�㈣�岃�块棶瓒冲�熷唴瀛樼殑鏉冮檺锛屼篃浼氶亣鍒颁笌澶т俊鎭�鍖呮湁鍏崇殑濂囨��闂�棰樸�傚�傛灉鎬�鐤戝嚭鐜颁簡璇ユ儏鍐碉紝璇峰皾璇曞湪mysqld_safe鑴氭湰寮�濮嬪�炲��ulimit -d 256000锛屽苟閲嶅惎mysqld銆�

瀵逛簬杩炴帴闂�棰橈紝鏈嶅姟鍣ㄩ敊璇�鏃ュ織鏄�鏈夌敤鐨勪俊鎭�婧愩�傝�峰弬瑙�5.11.1鑺傦紝鈥滈敊璇�鏃ュ織鈥�銆傚�傛灉鏈嶅姟鍣ㄦ槸鐢ㄢ��--log-warnings鈥濋�夐」鍚�鍔ㄧ殑锛屽湪閿欒��鏃ュ織涓�鍙�鑳戒細鍙戠幇涓嬭堪娑堟伅锛�

010301 14:38:23  Aborted connection 854 to db: 'users' user: 'josh'

濡傛灉鈥�Aborted connections鈥�锛堟斁寮冭繛鎺ワ級娑堟伅鍑虹幇鍦ㄩ敊璇�鏃ュ織涓�锛屽彲鑳界殑鍘熷洜鏄�锛�

1.    瀹㈡埛绔�绋嬪簭鍦ㄩ��鍑轰箣鍓嶆湭璋冪敤mysql_close()銆�

2.    瀹㈡埛绔�鐨勭┖闂叉椂闂磋秴杩�wait_timeout鎴�interactive_timeout绉掞紝鏈�鍚戞湇鍔″櫒鍙戝嚭浠讳綍璇锋眰銆�璇峰弬瑙�5.3.3鑺傦紝鈥滄湇鍔″櫒绯荤粺鍙橀噺鈥�銆�

3.    瀹㈡埛绔�鍦ㄦ暟鎹�浼犺緭涓�閫旂獊鐒剁粨鏉熴��

鍑虹幇杩欑被鎯呭喌鏃讹紝鏈嶅姟鍣ㄥ皢澧炲姞鈥�Aborted_clients鈥�锛堟斁寮冨�㈡埛绔�锛夌姸鎬佸彉閲忋��

鍑虹幇涓嬭堪鎯呭喌鏃讹紝鏈嶅姟鍣ㄥ皢澧炲姞鈥�Aborted_clients鈥�锛堟斁寮冨�㈡埛绔�锛夌姸鎬佸彉閲忋��

路         瀹㈡埛绔�涓嶅叿鏈夎繛鎺ヨ嚦鏁版嵁搴撶殑鏉冮檺銆�

路         瀹㈡埛绔�閲囩敤浜嗕笉姝ｇ‘鐨勫瘑鐮併��

路         杩炴帴淇℃伅鍖呬笉鍚�姝ｇ‘淇℃伅銆�

路         鑾峰彇杩炴帴淇℃伅鍖呯殑鏃堕棿瓒呰繃connect_timeout绉掋��璇峰弬瑙�5.3.3鑺傦紝鈥滄湇鍔″櫒绯荤粺鍙橀噺鈥�銆�

濡傛灉鍑虹幇杩欑被鎯呭喌锛屽彲鑳借〃鏄庢煇浜烘�ｈ瘯鍥句镜鍏ヤ綘鐨勬湇鍔″櫒锛�

瀵逛簬鏀惧純瀹㈡埛绔�鎴栨斁寮冭繛鎺ラ棶棰橈紝鍏朵粬鍙�鑳界殑婧愬簲鍖呮嫭锛�

路         涓�Linux涓�璧蜂娇鐢ㄤ互澶�缃戝崗璁�锛屽崐鍙屽伐鎴栧叏鍙屽伐銆傚緢澶�Linux浠ュお缃戦┍鍔ㄥ潎瀛樺湪璇ョ己闄枫�傚簲閫氳繃FTP鍦ㄥ�㈡埛绔�鍜屾湇鍔″櫒鏈哄櫒涔嬮棿浼犺緭澶ф枃浠舵潵娴嬭瘯璇ョ己闄枫�傚�傛灉浼犺緭澶勪��burst-pause-burst-pause锛堢垎鍙�-鏆傚仠-鐖嗗彂-鏆傚仠锛夋ā寮忥紝琛ㄦ槑浣犻亣鍒颁簡Linux鍙屽伐鏁呴殰銆傚敮涓�鐨勮В鍐虫柟娉曟槸锛屽皢缃戝崱鍜�Hub/浜ゆ崲鍣ㄧ殑鍙屽伐妯″紡鍒囨崲涓哄叏鍙屽伐鎴栧崐鍙屽伐锛屽苟瀵圭粨鏋滆繘琛屾祴璇曚互纭�瀹氭渶浣宠�剧疆銆�

路         涓庣嚎绋嬪簱鏈夊叧鐨勬煇浜涢棶棰樺�艰嚧璇诲彇涓�鏂�銆�

路         閰嶇疆涓嶈壇鐨�TCP/IP銆�

路         鏈夐棶棰樼殑浠ュお缃戙��Hub銆佷氦鎹㈠櫒銆佺數缂嗙瓑銆備粎鑳介�氳繃鏇存崲纭�浠舵墠鑳芥伆褰撹瘖鏂�銆�

路         鍙橀噺max_allowed_packet杩囧皬鎴�鏌ヨ�㈣�佹眰鐨勫唴瀛樿秴杩囦负mysqld鍒嗛厤鐨勫唴瀛樸�傝�峰弬瑙�A.2.9鑺傦紝鈥滀俊鎭�鍖呰繃澶р��銆�

鍙﹁�峰弬瑙�A.2.8鑺傦紝鈥淢ySQL鏈嶅姟鍣ㄤ笉鍙�鐢ㄢ��銆�

琛ㄥ凡婊￠敊璇�鍑虹幇鐨勬柟寮忔湁鏁扮�嶏細

路         浣犳�ｅ湪浣跨敤浣庝��3.23鐗堢殑MySQL鏈嶅姟鍣�锛岃�屼笖鈥滃唴瀛樹腑鈥濅复鏃惰〃瓒呰繃浜�tmp_table_size瀛楄妭銆�瑕佹兂閬垮厤璇ラ棶棰橈紝鍙�浣跨敤鈥�-O tmp_table_size=val鈥濋�夐」浠ヤ究mysqld澧炲姞涓存椂琛ㄧ殑澶у皬锛屾垨鍦ㄥ彂鍑烘湁闂�棰樼殑鏌ヨ��涔嬪墠锛屼娇鐢�SQL閫夐」SQL_BIG_TABLES銆�璇峰弬瑙�13.5.3鑺傦紝鈥淪ET璇�娉曗��銆�

涔熷彲浠ヤ娇鐢ㄢ��--big-tables鈥濋�夐」鍚�鍔�mysqld銆傚畠涓庝娇鐢ㄩ拡瀵规墍鏈夋煡璇㈢殑SQL_BIG_TABLES瀹屽叏鐩稿悓銆�

鑷�MySQL 3.23璧凤紝璇ラ棶棰樺簲涓嶅啀鍑虹幇銆傚�傛灉鈥滃唴瀛樹腑鈥濅复鏃惰〃瓒呰繃tmp_table_size锛屾湇鍔″櫒浼氳嚜鍔ㄥ皢鍏惰浆鎹�涓哄熀浜庣�佺洏鐨�MyISAM琛ㄣ��

路         浣犳�ｅ湪浣跨��InnoDB琛�锛�骞惰秴鍑轰簡InnoDB琛ㄧ┖闂淬��鍦ㄨ�ユ儏鍐典笅锛岃В鍐虫柟娉曟槸澧炲��InnoDB琛ㄧ┖闂淬�傝�峰弬瑙�15.2.7鑺傦紝鈥滄坊鍔犲拰鍒犻櫎InnoDB鏁版嵁鍜屾棩蹇楁枃浠�鈥�銆�

路         浣犳�ｅ湪浠呮敮鎸�2GB鏂囦欢鐨勬搷浣滅郴缁熶笂浣跨敤ISAM鎴�MyISAM琛�锛屾暟鎹�鏂囦欢鎴栫储寮曟枃浠惰揪鍒颁簡璇ラ檺鍒跺�笺��

路         浣犳�ｅ湪浣跨��MyISAM琛�锛岃�屼笖琛ㄦ墍闇�鐨勭┖闂磋秴杩囧唴閮ㄦ寚閽堝厑璁哥殑澶у皬銆傚�傛灉鍦ㄥ垱寤鸿〃鏃舵湭鎸囧��MAX_ROWS琛�锛�MySQL灏嗕娇鐢�myisam_data_pointer_size绯荤粺鍙橀噺銆�榛樿�ゅ�间负6瀛楄妭锛屽畠瓒充互瀹圭撼65536TB鏁版嵁銆傝�峰弬瑙�5.3.3鑺傦紝鈥滄湇鍔″櫒绯荤粺鍙橀噺鈥�銆�

浣跨敤璇ヨ��鍙ワ紝鍙�妫�鏌ユ渶澶ф暟鎹�/绱㈠紩澶у皬锛�

SHOW TABLE STATUS FROM database LIKE 'tbl_name';

涔熷彲浠ヤ娇鐢�myisamchk -dv /path/to/table-index-file銆�

濡傛灉鎸囬拡澶у皬杩囧皬锛屽彲浣跨敤ALTER TABLE鏇存�ｈ�ラ棶棰橈細

ALTER TABLE tbl_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=nnn;

浠呭簲涓哄叿鏈�BLOB鎴�TEXT鍒楃殑琛ㄦ寚瀹�AVG_ROW_LENGTH銆�鍦ㄨ�ユ儏鍐典笅锛�MySQL涓嶈兘浠呮牴鎹�琛屾暟浼樺寲鎵�闇�鐨勭┖闂淬��

濡傛灉瀵规煇浜涙煡璇㈤亣鍒颁笅杩扮被鍨嬬殑閿欒��锛屽畠鎰忓懗鐫�MySQL涓嶈兘涓轰复鏃剁洰褰曚笅鐨勭粨鏋滈泦鍒涘缓涓存椂鏂囦欢锛�

鏃犳硶鍒涘缓/鍐欏叆鏂囦欢'\\sqla3fe_0.ism'銆�

鍓嶈堪閿欒��鏄�Windows骞冲彴涓婄殑鍏稿瀷娑堟伅锛�Unix骞冲彴涓婄殑娑堟伅涓庝箣绫讳技銆�

涓�绉嶆洿姝ｆ柟寮忔槸浣跨敤鈥�--tmpdir鈥濋�夐」鍚�鍔�mysqld锛屾垨鍦ㄩ�夐」鏂囦欢鐨�[mysqld]閮ㄥ垎澧炲姞璇ラ�夐」銆�渚嬪�傦紝瑕佹兂鎸囧畾鐩�褰�C:\temp锛屽彲浣跨敤锛�

[mysqld]

tmpdir=C:/temp

鐩�褰�C:\temp蹇呴』瀛樺湪锛�骞舵湁瓒冲�熺殑绌洪棿鍏佽��MySQL鍐欏叆瀹冦�傝�峰弬瑙�4.3.2鑺傦紝鈥滀娇鐢ㄩ�夐」鏂囦欢鈥�銆�

璇ラ敊璇�鐨勫彟涓�涓�鍘熷洜鍙�鑳芥槸璁稿彲浜嬪疁銆傝�风‘璁�MySQL鏈嶅姟鍣ㄨ兘澶熷啓鍏�tmpdir鐩�褰曘��

姝ゅ�栵紝杩樼敤浣跨��perror妫�鏌ラ敊璇�浠ｇ爜銆傛湇鍔″櫒鏃犳硶鍐欏叆琛ㄧ殑涓�涓�鍘熷洜鏄�鏂囦欢绯荤粺宸叉弧銆�

shell> perror 28

閿欒��浠ｇ爜28锛氱�佺洏涓婃棤鍓╀綑绌洪棿銆�

濡傛灉閬囧埌涓嬭堪閿欒��锛岃〃绀哄綋鍚�鍔�mysqld鏃舵垨閲嶆柊鍔犺浇鎺堟潈琛ㄦ椂锛屽湪鐢ㄦ埛琛ㄤ腑鍙戠幇鍏锋湁闈炴硶瀵嗙爜鐨勮处鎴枫��

鍙戠幇鐢ㄦ埛'some_user'@'some_host'瀵嗙爜閿欒��锛氬拷鐣ョ敤鎴枫��

浣滀负鍏剁粨鏋滐紝璁稿彲绯荤粺灏嗙畝鍗曞拷鐣ヨ处鎴枫��

鍦ㄤ笅闈㈢殑浠嬬粛涓�锛屾寚鏄庝簡鍙�鑳界殑鍘熷洜鍜岄棶棰樼殑鏇存�ｆ帾鏂斤��

1.    鎴栬�革紝浣犳�ｆ墦绠楃敤鏃х殑鐢ㄦ埛琛ㄨ繍琛屾柊鐗堟湰鐨�mysqld銆傛墽琛�mysqlshow mysql user妫�鏌�Password锛堝瘑鐮侊級鍒楁槸鍚︾煭浜�16涓�瀛楃�︼紝閫氳繃璇ユ柟寮忓彲妫�鏌ヨ�ラ棶棰樸�傚�傛灉缁撴灉鏄�鑲�瀹氱殑锛屽彲杩愯�岃剼鏈�/add_long_password鑴氭湰鏇存�ｈ�ラ棶棰樸��

2.    璐︽埛鍏锋湁鏃х殑瀵嗙爜锛�8瀛楃�﹂暱锛夛紝鑰屼笖鏈�浣跨敤鈥�--old-protocol鈥濋�夐」鍚�鍔�mysqld銆傛洿鏂扮敤鎴疯〃涓�鐨勮处鎴凤紝浣夸箣鍏锋湁鏂扮殑瀵嗙爜锛屾垨浣跨敤鈥�--old-protocol鈥濋�夐」閲嶅惎mysqld銆�

3.    鍦ㄧ敤鎴疯〃涓�鏈�浣跨敤PASSWORD()鍑芥暟鎸囧畾浜嗗瘑鐮併��浣跨敤mysql鐢ㄦ柊瀵嗙爜鏇存柊鐢ㄦ埛琛ㄤ腑鐨勮处鎴凤紝鍔″繀浣跨敤PASSWORD()鍑芥暟锛�

4.           mysql> UPDATE user SET Password=PASSWORD('newpwd')

5.               -> WHERE User='some_user' AND Host='some_host';

濡傛灉閬囧埌涓嬭堪閿欒��涔嬩竴锛岄�氬父鎰忓懗鐫�褰撳墠鏁版嵁搴撲腑涓嶅瓨鍦ㄥ叿鏈夌粰瀹氬悕绉扮殑琛�锛�

琛�'tbl_name'涓嶅瓨鍦�

鏃犳硶鎵惧埌鏂囦欢锛�'tbl_name' (errno: 2)

鍦ㄦ煇浜涙儏鍐典笅锛岃〃鎴栬�稿瓨鍦�锛屼絾鏈�姝ｇ‘寮曠敤瀹冿��

路         鐢变簬MySQL浣跨敤鐩�褰曞拰鏂囦欢鏉ヤ繚瀛樻暟鎹�搴撳拰琛�锛屽�傛灉瀹冧滑浣嶄簬鍖哄垎鏂囦欢鍚嶅ぇ灏忓啓鐨勬枃浠剁郴缁熶笂锛屾暟鎹�搴撳拰琛ㄥ悕涔熷尯鍒嗘枃浠跺ぇ灏忓啓銆�

路         鍗充娇瀵逛簬涓嶅尯鍒嗗ぇ灏忓啓鐨勬枃浠剁郴缁燂紝濡�Windows锛屽湪鏌ヨ�㈠唴瀵圭粰瀹氳〃鐨勬墍鏈夊紩鐢ㄥ繀椤讳娇鐢ㄧ浉鍚岀殑澶у皬鍐欍��

鍙�浠ヤ娇鐢�SHOW TABLES妫�鏌ヤ綅浜庡綋鍓嶆暟鎹�搴撲腑鐨勮��銆傝�峰弬瑙�13.5.4鑺傦紝鈥淪HOW璇�娉曗��銆�

濡傛灉瀛樺湪瀛楃�﹂泦闂�棰橈紝鍙�鑳戒細閬囧埌涓嬭堪閿欒��锛�

MySQL杩炴帴澶辫触锛氭棤娉曞垵濮嬪寲瀛楃�﹂��charset_name銆�

瀵艰嚧璇ラ敊璇�鐨勫師鍥狅��

路         瀛楃�﹂泦涓哄�氬瓧鑺傚瓧绗﹂泦锛屼絾瀹㈡埛绔�涓嶆敮鎸佽�ュ瓧绗﹂泦銆傚湪璇ユ儏鍐典笅锛岄渶瑕佷娇鐢ㄢ��--with-charset=charset_name鈥濇垨鈥�--with-extra-charsets=charset_name鈥濋�夐」杩愯��configure浠ラ噸鏂扮紪璇戝�㈡埛绔�銆傝�峰弬瑙�2.8.2鑺傦紝鈥滃吀鍨嬮厤缃�閫夐」鈥�銆�

鎵�鏈夌殑鏍囧噯MySQL浜岃繘鍒舵枃浠跺潎鏄�閲囩敤鈥�--with-extra-character-sets=complex鈥濈紪璇戠殑锛岃兘澶熸敮鎸佹墍鏈夌殑澶氬瓧鑺傚瓧绗﹂泦銆傝�峰弬瑙�5.10.1鑺傦紝鈥滄暟鎹�鍜屾帓搴忕敤瀛楃�﹂泦鈥�銆�

路         瀛楃�﹂泦鏄�鏈�缂栬瘧鍒�mysqld涓�鐨勭畝鍗曞瓧绗﹂泦锛岃�屼笖瀛楃�﹂泦瀹氫箟鏂囦欢涓嶅湪瀹㈡埛绔�棰勬湡鐨勪綅缃�銆�

鍦ㄨ�ユ儏鍐典笅锛岄渶瑕侀噰鍙栦笅杩版柟娉曚箣涓�瑙ｅ喅闂�棰橈��

1.    閲嶆柊缂栬瘧瀹㈡埛绔�锛屼娇涔嬫敮鎸佸瓧绗﹂泦銆傝�峰弬瑙�2.8.2鑺傦紝鈥滃吀鍨嬮厤缃�閫夐」鈥�銆�

2.    涓哄�㈡埛绔�鎸囧畾瀛楃�﹂泦瀹氫箟鏂囦欢鎵�鍦ㄧ殑鐩�褰曘�傚�逛簬寰堝�氬�㈡埛绔�锛屽彲浣跨敤鈥�--character-sets-dir鈥濋�夐」瀹屾垚璇ヤ换鍔°��

3.    灏嗗瓧绗﹂泦瀹氫箟鏂囦欢澶嶅埗鍒板�㈡埛绔�棰勬湡鐨勪綅缃�銆�

濡傛灉閬囧埌鈥�ERROR '...'鏈�鍙戠��(errno: 23)鈥�锛�鈥�鏃犳硶鎵撳紑鏂囦欢锛�... (errno: 24)鈥�锛屾垨鏉ヨ嚜MySQL鐨勫叿鏈�errno 23鎴�errno 24鐨勫叾瀹冮敊璇�锛屽畠琛ㄧず鏈�涓�MySQL鏈嶅姟鍣ㄥ垎閰嶈冻澶熺殑鏂囦欢鎻忚堪绗�銆備綘鍙�浠ヤ娇鐢�perror瀹炵敤宸ュ叿鏉ヤ簡瑙ｉ敊璇�缂栧彿鐨勫惈涔夛��

shell> perror 23

閿欒��浠ｇ爜23锛氭枃浠惰〃婧㈠嚭

shell> perror 24

閿欒��浠ｇ爜24锛氭墦寮�鏂囦欢杩囧��

shell> perror 11

閿欒��浠ｇ爜11锛氳祫婧愭殏鏃朵笉鍙�鐢�

杩欓噷鐨勯棶棰樻槸锛�mysqld姝ｈ瘯鍥惧悓鏃舵墦寮�杩囧�氱殑鏂囦欢銆備綘鍙�浠ラ�氱煡mysqld涓嶈�佷竴娆℃墦寮�杩囧�氭枃浠讹紝鎴栧�炲姞mysqld鍙�鐢ㄦ枃浠舵弿杩扮�︾殑鏁扮洰銆�

瑕佹兂閫氱煡mysqld灏嗕竴娆℃墦寮�鐨勬枃浠舵帶鍒跺湪杈冨皬鐨勬暟鐩�涓婏紝鍙�闄嶄綆table_cache绯荤粺鍙橀噺鐨勫�硷紙锛夛紝浠庤�屽噺灏戣〃楂橀�熺紦鍐诧紙榛樿�ゅ�间负64锛夈�傞檷浣�max_connections鐨勫�间篃鑳介檷浣�鎵撳紑鏂囦欢鐨勬暟鐩�锛堥粯璁ゅ�间负100锛夈��

瑕佹兂鏇存敼mysqld鍙�鐢ㄧ殑鏂囦欢鎻忚堪绗︾殑鏁扮洰锛屽彲鍦�mysqld_safe涓婁娇鐢ㄢ��--open-files-limit鈥�閫夐」鎴栬�剧��锛堣嚜MySQL 3.23.30寮�濮嬶級open_files_limit绯荤粺鍙橀噺銆傝�峰弬瑙�5.3.3鑺傦紝鈥滄湇鍔″櫒绯荤粺鍙橀噺鈥�銆傝�剧疆杩欎簺鍊肩殑鏈�绠�鍗曟柟寮忔槸鍦ㄩ�夐」鏂囦欢涓�澧炲��1涓�閫夐」銆傝�峰弬瑙�4.3.2鑺傦紝鈥滀娇鐢ㄩ�夐」鏂囦欢鈥�銆傚�傛��mysqld鐨勭増鏈�杈冧綆锛屼笉鏀�鎸佽�剧疆鎵撳紑鏂囦欢鐨勬暟鐩�锛屽彲缂栬��mysqld_safe鑴氭湰銆傚湪鑴氭湰涓�鏈�1涓�娉ㄩ噴鎺夌殑琛�ulimit -n 256銆備綘鍙�浠ュ垹闄�鈥�#鈥欏瓧绗﹀彇娑堝�硅�ヨ�岀殑娉ㄩ噴锛屾洿鏀规暟鍊�256锛屼互璁剧疆mysqld鍙�鐢ㄧ殑鏂囦欢鎻忚堪绗︽暟鐩�銆�

鈥�--open-files-limit鈥�鍜�ulimit鑳藉�熷�炲姞鏂囦欢鎻忚堪绗︾殑鏁扮洰锛屼絾鏈�楂樹笉鑳借秴杩囨搷浣滅郴缁熼檺鍒剁殑鏁扮洰銆傛�ゅ�栬繕鏈�1涓�鈥滅‖鈥濋檺鍒讹紝浠呭綋浠ユ牴鐢ㄦ埛韬�浠藉惎鍔�mysqld_safe鎴�mysqld鏃舵墠鑳借�嗙洊瀹冿紙璇疯�颁綇锛屽湪璇ユ儏鍐典笅锛岃繕闇�浣跨敤鈥�--user鈥濋�夐」鍚�鍔ㄦ湇鍔″櫒锛屼互渚垮湪鍚�鍔ㄥ悗涓嶅啀浠ユ牴鐢ㄦ埛韬�浠界户缁�杩愯�岋級銆傚�傛灉闇�瑕佸�炲姞鎿嶄綔绯荤粺闄愬埗鐨勫�瑰悇杩涚▼鍙�鐢ㄦ枃浠舵弿杩扮�︾殑鏁扮洰锛岃�峰弬闃呯郴缁熸枃妗ｃ��

娉ㄩ噴锛�濡傛灉杩愯��tcsh shell锛�ulimit涓嶅伐浣滐紒璇锋眰褰撳墠闄愬埗鍊兼椂锛�tcsh杩樿兘閫氭姤涓嶆�ｇ‘鐨勫�笺�傚湪璇ユ儏鍐典笅锛屽簲浣跨敤sh鍚�鍔�mysqld_safe銆�

褰撲綘閾炬帴鍒板簲鐢ㄧ▼搴忎互浣跨敤MySQL瀹㈡埛绔�搴撴椂锛屽彲鑳戒細閬囧埌浠�mysql_寮�濮嬬殑鏈�瀹氫箟寮曠敤閿欒��锛屽�備笅鎵�绀猴細

/tmp/ccFKsdPa.o: 鍦ㄥ嚱鏁�`main'涓�:

/tmp/ccFKsdPa.o(.text+0xb): 瀵�`mysql_init'鐨勬湭瀹氫箟寮曠敤銆�

/tmp/ccFKsdPa.o(.text+0x31): 瀵�`mysql_real_connect'鐨勬湭瀹氫箟寮曠敤銆�

/tmp/ccFKsdPa.o(.text+0x57): 瀵�`mysql_real_connect'鐨勬湭瀹氫箟寮曠敤銆�

/tmp/ccFKsdPa.o(.text+0x69): 瀵�`mysql_error'鐨勬湭瀹氫箟寮曠敤銆�

/tmp/ccFKsdPa.o(.text+0x9a): 瀵�`mysql_close'鐨勬湭瀹氫箟寮曠敤銆�

閫氳繃鍦ㄩ摼鎺ュ懡浠ゅ悗澧炲姞鈥�-Ldir_path -lmysqlclient鈥濋�夐」锛屽簲鑳借В鍐宠�ラ棶棰橈紝鍏朵腑锛�dir_path浠ｈ〃瀹㈡埛绔�搴撴墍鍦ㄧ洰褰曠殑璺�寰勫悕銆傝�佹兂纭�瀹氭�ｇ‘鐨勭洰褰曪紝鍙�灏濊瘯涓嬭堪鍛戒护锛�

shell> mysql_config --libs

鏉ヨ嚜mysql_config鐨勮緭鍑哄彲鑳戒細鎸囨槑搴斿湪閾炬帴鍛戒护涓婃寚瀹氱殑鍏朵粬搴撱��

瀵逛簬闈炲帇缂╂垨鍘嬬缉鍑芥暟锛屽�傛灉閬囧埌鏈�瀹氫箟寮曠敤閿欒��锛屽彲鍦ㄩ摼鎺ュ懡浠ゅ悗娣诲姞-lz锛屽苟鍐嶆�″皾璇曘��

瀵逛簬搴斿湪绯荤粺涓婂瓨鍦ㄧ殑鍑芥暟锛堝��connect锛夛紝濡傛灉閬囧埌鏈�瀹氫箟寮曠敤閿欒��锛岃�锋��鏌ョ浉鍏冲嚱鏁扮殑鎵嬪唽椤碉紝浠ヤ究纭�瀹氬簲鍦ㄩ摼鎺ュ懡浠や笂澧炲姞鍝�浜涘簱銆�

瀵逛簬绯荤粺涓婁笉瀛樺湪鐨勫嚱鏁帮紝鍙�鑳戒細閬囧埌鏈�瀹氫箟寮曠敤閿欒��锛屽�備笅鎵�绀猴細

mf_format.o(.text+0x201): 瀵�`__lxstat'鐨勬湭瀹氫箟寮曠敤銆�

瀹冮�氬父鎰忓懗鐫�浣犵殑MySQL瀹㈡埛绔�搴撴槸鍦ㄤ笌浣犵殑绯荤粺涓�100锛呭吋瀹圭殑绯荤粺涓婄紪璇戠殑銆傚湪璇ユ儏鍐典笅锛屽簲涓嬭浇鏈�鏂扮殑MySQL婧愮爜鍒嗗彂鐗堬紝骞惰嚜宸辩紪璇�MySQL銆傝�峰弬瑙�2.8鑺傦紝鈥滀娇鐢ㄦ簮鐮佸垎鍙戠増瀹夎�匨ySQL鈥�銆�

褰撲綘璇曞浘鎵ц��MySQL绋嬪簭鏃讹紝鍙�鑳戒細閬囧埌杩愯�屾椂鏈�瀹氫箟寮曠敤閿欒��銆傚�傛灉杩欑被閿欒��鎸囨槑浜嗕��mysql_寮�濮嬬殑绗﹀彿锛�鎴栨寚鏄庢湭鍙戠幇mysqlclient搴�锛岃繖鎰忓懗鐫�浣犵殑绯荤粺鏃犳硶鎵惧埌鍏变韩鐨�libmysqlclient.so搴撱��瀵瑰叾鐨勬洿姝ｆ柟寮忔槸锛岄�氱煡绯荤粺鍦ㄥ簱鎵�鍦ㄤ綅缃�鎼滅储鍏变韩搴撱�傝�蜂娇鐢ㄤ笌绯荤粺鐩搁�傚簲鐨勪笅杩版柟娉曪細

路         灏�libmysqlclient.so鎵�鍦ㄧ洰褰曠殑璺�寰勬坊鍔犲��LD_LIBRARY_PATH鐜�澧冨彉閲忎腑銆�

路         灏�libmysqlclient.so鎵�鍦ㄧ洰褰曠殑璺�寰勬坊鍔犲��LD_LIBRARY鐜�澧冨彉閲忎腑銆�

路         灏�libmysqlclient.so鎷疯礉鍒板彲琚�绯荤粺鎼滅储鐨勭洰褰曚笅锛屽��/lib锛岀劧鍚庨�氳繃鎵ц��ldconfig鏇存柊鍏变韩搴撲俊鎭�銆�

瑙ｅ喅璇ラ棶棰樼殑鍙︿竴绉嶆柟娉曟槸锛屼互闈欐�佹柟寮忓皢浣犵殑绋嬪簭涓庘��-static鈥濋�夐」閾炬帴鍦ㄤ竴璧凤紝鎴栧湪閾炬帴浠ｇ爜涔嬪墠鍒犻櫎鍔ㄦ��MySQL搴撱�備娇鐢ㄧ��2绉嶆柟娉曚箣鍓嶏紝搴旂‘淇濇病鏈変娇鐢ㄥ姩鎬佸簱鐨勫叾瀹冪▼搴忋��

鍦�Windows骞冲彴涓婏紝鑳藉�熶娇鐢ㄦ櫘閫氱敤鎴疯处鎴蜂互Windows鏈嶅姟鏂瑰紡杩愯�屾湇鍔″櫒銆�

鍦�Unix骞冲彴涓婏紝涓嶆槸浠讳綍鐢ㄦ埛閮借兘鍚�鍔ㄥ苟杩愯��MySQL鏈嶅姟鍣�mysqld鐨�銆備絾鏄�锛屽�勪簬瀹夊叏鏂归潰鐨勫師鍥狅紝搴旈伩鍏嶄互Unix鏍圭敤鎴疯韩浠借繍琛屾湇鍔″櫒銆傝�佹兂鏇存��mysqld锛屼娇涔嬭兘浠ユ�ｅ父鐨勬棤鐗规��Unix鐢ㄦ埛user_name韬�浠借繍琛岋紝蹇呴』閲囧彇涓嬭堪姝ラ�わ細

濡傛灉鏈嶅姟鍣ㄦ�ｅ湪杩愯�岋紝鍋滄�㈠畠锛堜娇鐢�mysqladmin shutdown锛夈��

鏇存敼鏁版嵁搴撶洰褰曞拰鏂囦欢锛屽厑璁�user_name璇诲啓鍏朵腑鐨勬枃浠讹紙鍙�鑳介渶瑕佷互Unix鏍圭敤鎴疯韩浠藉畬鎴愯繖绫昏�剧疆锛夛��

shell> chown -R user_name /path/to/mysql/datadir

濡傛灉鏈�杩欐牱鍋氾紝褰撲��user_name韬�浠借繍琛屾椂锛屾湇鍔″櫒鏃犳硶璁块棶鏁版嵁搴撴垨琛ㄣ��

濡傛灉MySQL鏁版嵁鐩�褰曚笅鐨勭洰褰曟垨鏂囦欢閲囩敤鐨勬槸绗﹀彿閾炬帴锛岃繕闇�璺熻釜杩欎簺閾炬帴锛屽苟鏇存敼瀹冧滑鎸囧悜鐨勭洰褰曞拰鏂囦欢銆�chown 鈥揜鍙�甯�鍔╀綘璺熻釜绗﹀彿閾炬帴銆�

浠�user_name韬�浠藉惎鍔ㄦ湇鍔″櫒銆傚�傛灉浣犳�ｅ湪浣跨敤MySQL 3.22鎴栨洿楂樼増鏈�锛屽彟涓�绉嶅彲閫夋柟寮忔槸锛屼互Unix鏍圭敤鎴疯韩浠藉惎鍔�mysqld锛屽苟浣跨敤--user=user_name閫夐」銆�mysqld鍚�鍔�锛岀劧鍚庡湪鎺ュ彈浠讳綍杩炴帴鍓嶏紝鍒囨崲鑷�Unix鐢ㄦ埛user_name骞朵互璇ョ敤鎴疯韩浠借繍琛屻��

瑕佹兂鍦ㄧ郴缁熷惎鍔ㄦ椂鑷�鍔ㄤ互缁欏畾鐨勭敤鎴疯韩浠藉惎鍔ㄦ湇鍔″櫒锛屽彲鍦ㄦ湇鍔″櫒鏁版嵁鐩�褰曚笅鐨�/etc/my.cnf閫夐」鏂囦欢鎴�my.cnf閫夐」鏂囦欢鐨�[mysqld]缁勪腑锛岄�氳繃澧炲姞鐢ㄦ埛閫夐」鏉ユ寚瀹氱敤鎴峰悕銆�銆備緥濡傦細

[mysqld]

user=user_name

濡傛灉浣犵殑Unix鏈哄櫒鏈�韬�骞朵笉瀹夊叏锛屽簲鍦ㄦ巿鏉冭〃涓�涓�MySQL鏍硅处鎴锋寚瀹氬瘑鐮併�傚�備笉鐒讹紝浠讳綍鍦ㄨ�ユ満鍣ㄤ笂鍏锋湁鐧诲綍璐︽埛鐨勭敤鎴烽兘鑳戒娇鐢ㄢ��--user=root鈥濋�夐」杩愯��mysql瀹㈡埛绔�锛屽苟鎵ц�屼换浣曟搷浣溿�傚湪浠讳綍鎯呭喌涓嬪潎搴斾负MySQL璐︽埛鎸囧畾瀵嗙爜锛岃繖鏄�涓�濂戒富鎰忥紝灏ゅ叾鏄�鍦ㄦ湇鍔″櫒涓绘満涓婂瓨鍦ㄥ叾浠栫櫥褰曡处鎴锋椂锛屾洿鏄�濡傛�ゃ�傝�峰弬瑙�2.9鑺傦紝鈥滃畨瑁呭悗鐨勮�剧疆鍜屾祴璇曗��銆�

濡傛灉閬囧埌涓庢枃浠惰�稿彲鏈夊叧鐨勯棶棰橈紝鍙�鑳芥暟鍚�鍔�mysqld鏃�UMASK鐜�澧冨彉閲忚�剧疆寰椾笉姝ｇ‘銆備緥濡傦紝褰撲綘鍒涘缓琛ㄦ椂锛�MySQL鍙�鑳戒細鍙戝嚭涓嬭堪閿欒��娑堟伅锛�

ERROR: 鏃犳硶鎵惧埌鏂囦欢锛�'path/with/filename.frm' (Errcode: 13)

UMASK鐨勯粯璁ゅ�兼槸0660銆傞�氳繃涓嬭堪鏂瑰紡鍚�鍔�mysqld_safe锛屽彲鏀瑰彉璇ユ儏鍐碉細

shell> UMASK=384  # = 600 in octal

shell> export UMASK

shell> mysqld_safe &

鍦ㄩ粯璁ゆ儏鍐典笅锛�MySQL鐢�0700鐨勮�稿彲鍒涘缓鏁版嵁搴撳��RAID鐩�褰曘�備綘鍙�浠ラ�氳繃璁剧疆UMASK_DIR鍙橀噺鏇存敼璇ヨ�屼负銆傚�傛灉浣犺�剧疆浜嗗畠鐨勫�硷紝灏嗕娇鐢ㄧ粍鍚堢殑UMASK鍜�UMASK_DIR鍊煎垱寤烘柊鐩�褰曘�備緥濡�锛屽�傛灉浣犳墦绠椾负鎵�鏈夋柊鐨勭洰褰曟巿浜堢粍璁块棶鏉冮檺锛屽彲锛�

shell> UMASK_DIR=504  # = 770 in octal

shell> export UMASK_DIR

shell> mysqld_safe &

鍦�MySQL 3.23.25鍜屾洿楂樼増鏈�涓�锛屽�傛灉鏄�浠�0寮�濮嬬殑锛�MySQL灏嗚�や��UMASK鍜�UMASK_DIR鐨勫�煎潎閲囩敤鍏�杩涘埗褰㈠紡銆�

璇峰弬瑙�闄勫綍F锛�鐜�澧冨彉閲�銆�

濡傛灉浣犱粠鏈�涓�MySQL璁剧疆鏍圭敤鎴峰瘑鐮侊紝鏈嶅姟鍣ㄥ湪浠ユ牴鐢ㄦ埛韬�浠借繘琛岃繛鎺ユ椂涓嶉渶瑕佸瘑鐮併�備絾鏄�锛屽缓璁�浣犱负姣忎釜璐︽埛璁剧疆瀵嗙爜銆傝�峰弬瑙�5.6.1鑺傦紝鈥滈�氱敤瀹夊叏鎸囧崡鈥�銆�

濡傛灉浣犱互鍓嶈�剧疆浜嗘牴鐢ㄦ埛瀵嗙爜锛屼絾鍗村繕璁颁簡璇ュ瘑鐮侊紝鍙�璁剧疆鏂扮殑瀵嗙爜銆備笅杩版�ラ�ゆ槸閽堝��Windows骞冲彴鐨勩�傚湪鏈�鑺傚悗闈㈢殑鍐呭�逛腑锛屼粙缁嶄簡閽堝��Unix骞冲彴鐨勬�ラ�ゃ��

鍦�Windows骞冲彴涓嬶紝璇ユ�ラ�ゆ槸锛�

浠ョ郴缁熺�＄悊鍛樿韩浠界櫥褰曞埌绯荤粺銆�

濡傛灉MySQL鏈嶅姟鍣ㄦ�ｅ湪杩愯�岋紝鍋滄�㈠畠銆傚�逛簬浣滀��Windows鏈嶅姟杩愯�岀殑鏈嶅姟鍣�锛岃繘鍏ユ湇鍔＄�＄悊鍣�锛�

寮�濮嬭彍鍗�->鎺у埗闈㈡澘->绠＄悊宸ュ叿->鏈嶅姟

鐒跺悗鍦ㄥ垪琛ㄤ腑鎵惧嚭MySQL鏈嶅姟鍣�锛屽苟鍋滄�㈠畠銆�

濡傛灉鏈嶅姟鍣ㄤ笉鏄�浣滀负鏈嶅姟鑰岃繍琛岀殑锛屽彲鑳介渶瑕佷娇鐢ㄤ换鍔＄�＄悊鍣ㄦ潵寮哄埗鍋滄�㈠畠銆�

鍒涘缓1涓�鏂囨湰鏂囦欢锛屽苟灏嗕笅杩板懡浠ょ疆浜庡崟涓�琛屼腑锛�

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPassword');

鐢ㄤ换鎰忓悕绉颁繚瀛樿�ユ枃浠躲�傚湪鏈�渚嬩腑锛岃�ユ枃浠朵负C:\mysql-init.txt銆�

鎵撳紑鎺у埗鍙扮獥鍙ｏ紝杩涘叆DOS鍛戒护鎻愮ず锛�

寮�濮嬭彍鍗�->杩愯��-> cmd

鍋囧畾浣犲凡灏�MySQL瀹夎�呭��C:\mysql銆傚�傛灉浣犲��MySQL瀹夎�呭埌浜嗗彟涓�浣嶇疆锛岃�峰�逛笅杩板懡浠よ繘琛岀浉搴旂殑璋冩暣銆�

鍦�DOS鍛戒护鎻愮ず绗︿笅锛屾墽琛屽懡浠わ細

C:\> C:\mysql\bin\mysqld-nt --init-file=C:\mysql-init.txt

鍦ㄦ湇鍔″櫒鍚�鍔ㄦ椂锛屾墽琛岀敱鈥�--init-file鈥濋�夐」鍛藉悕鐨勬枃浠剁殑鍐呭�癸紝鏇存敼鏍圭敤鎴峰瘑鐮併�傚綋鏈嶅姟鍣ㄦ垚鍔熷惎鍔ㄥ悗锛屽簲鍒犻櫎C:\mysql-init.txt銆�

濡傛灉浣犱娇鐢�MySQL瀹夎�呭悜瀵煎畨瑁呬��MySQL锛屾垨璁搁渶瑕佹寚瀹氣��--defaults-file鈥�閫夐」锛�

C:\> C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld-nt.exe

         --defaults-file="C:\Program Files\MySQL\MySQL Server 5.1\my.ini"

         --init-file=C:\mysql-init.txt

浣跨敤鏈嶅姟绠＄悊鍣�锛屽彲鎵惧埌鎭板綋鐨勨��--defaults-file鈥�璁剧疆锛�

寮�濮嬭彍鍗�->鎺у埗闈㈡澘->绠＄悊宸ュ叿->鏈嶅姟

鍦ㄥ垪琛ㄤ腑鎵惧嚭MySQL鏈嶅姟锛屽彸鍑伙紝骞堕�夋嫨鈥滃睘鎬р�濋�夐」銆傚湪鍙�鎵ц�屽瓧娈电殑Path锛堣矾寰勶級涓�鍖呭��鈥�--defaults-file鈥�璁剧疆銆�

鍋滄��MySQL鏈嶅姟鍣�锛岀劧鍚庡湪姝ｅ父妯″紡涓嬮噸鍚�瀹冦�傚�傛灉浠ユ湇鍔℃柟寮忚繍琛屾湇鍔″櫒锛屽簲浠�Windows鏈嶅姟绐楀彛鍚�鍔ㄥ畠銆傚�傛灉浠ユ墜鍔ㄦ柟寮忓惎鍔ㄤ簡鏈嶅姟鍣�锛岃兘澶熷儚姝ｅ父鎯呭舰涓嬩竴鏍蜂娇鐢ㄥ懡浠ゃ��

搴旇兘浣跨敤鏂板瘑鐮佽繘琛岃繛鎺ャ��

鍦�Unix鐜�澧冧笅锛岄噸缃�鏍圭敤鎴峰瘑鐮佺殑姝ラ�ゅ�備笅锛�

浠�Unix鏍圭敤鎴疯韩浠姐�佹垨浠ヨ繍琛�mysqld鏈嶅姟鍣ㄧ殑鐩稿悓韬�浠界櫥褰曞埌绯荤粺銆�

鎵惧埌鍖呭惈鏈嶅姟鍣ㄨ繘绋�ID鐨�.pid鏂囦欢銆傝�ユ枃浠剁殑鍑嗙‘浣嶇疆鍜屽悕绉板彇鍐充簬浣犵殑鍒嗗彂鐗堛�佷富鏈哄悕鍜岄厤缃�銆傚父瑙佷綅缃�鏄�/var/lib/mysql/銆�/var/run/mysqld/鍜�/usr/local/mysql/data/銆�涓�鑸�鎯呭喌涓嬶紝鏂囦欢鍚嶇殑鎵╁睍鍚嶄��.pid锛屽苟浠�mysqld鎴栫郴缁熺殑涓绘満鍚嶅紑濮嬨��

鍦ㄤ笅杩板懡浠や腑浣跨敤.pid鏂囦欢鐨勮矾寰勫悕锛屽悜mysqld杩涚▼鍙戝嚭姝ｅ父鐨�kill锛堣�屼笉鏄�kill -9锛夛紝鍙�鍋滄��MySQL鏈嶅姟鍣�锛�

shell> kill `cat /mysql-data-directory/host_name.pid`

娉ㄦ剰锛�cat鍛戒护浣跨敤绗﹀彿鈥�`鈥濊�屼笉鏄�鈥溾�欌�濓細杩欎細浣�cat鐨勮緭鍑轰唬鍏ュ埌kill鍛戒护涓�銆�

鍒涘缓鏂囨湰鏂囦欢锛屽苟灏嗕笅杩板懡浠ゆ斁鍦ㄦ枃浠跺唴鐨�1琛屼笂锛�

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPassword');

鐢ㄤ换鎰忓悕绉颁繚瀛樻枃浠躲�傚�逛簬鏈�渚嬶紝鏂囦欢涓�~/mysql-init銆�

鐢ㄧ壒娈婄殑鈥�--init-file=~/mysql-init鈥濋�夐」閲嶅惎MySQL鏈嶅姟鍣�锛�

shell> mysqld_safe --init-file=~/mysql-init &

鏂囦欢init-file鐨勫唴瀹瑰湪鏈嶅姟鍣ㄥ惎鍔ㄦ椂鎵ц�岋紝鏇存敼鏍圭敤鎴峰瘑鐮併�傛湇鍔″櫒鎴愬姛鍚�鍔ㄥ悗锛屽簲鍒犻��~/mysql-init銆�

搴旇兘浣跨敤鏂板瘑鐮佽繘琛岃繛鎺ャ��

浣滀负鍙�閫夋柟寮忥紝鍦ㄤ换浣曞钩鍙颁笂锛屽彲浣跨敤mysql瀹㈡埛绔�璁剧疆鏂板瘑鐮侊紙浣嗚�ユ柟娉曚笉澶熷畨鍏�锛夛��

鍋滄��mysqld锛屽苟鐢ㄢ��--skip-grant-tables --user=root鈥�閫夐」閲嶅惎瀹�锛�Windows鐢ㄦ埛鍙�鐪佺��--user=root閮ㄥ垎锛夈��

浣跨敤涓嬭堪鍛戒护杩炴帴鍒�mysqld鏈嶅姟鍣�锛�

shell> mysql -u root

鍦�mysql瀹㈡埛绔�鍙戝嚭涓嬭堪璇�鍙ワ細

mysql> UPDATE mysql.user SET Password=PASSWORD('newpwd')

    ->                   WHERE User='root';

mysql> FLUSH PRIVILEGES;

鐢ㄦ墦绠椾娇鐢ㄧ殑瀹為檯鏍圭敤鎴峰瘑鐮佹浛鎹⑩��newpwd鈥濄��

搴旇兘浣跨敤鏂板瘑鐮佽繘琛岃繛鎺ャ��

姝ｅ紡鍙戝竷涔嬪墠锛屾瘡涓�MySQL鐗堟湰鍧囧湪寰堝�氬钩鍙颁笂杩涜�屼簡娴嬭瘯銆傝繖涓嶈〃绀�MySQL涓�涓嶅瓨鍦ㄧ己闄凤紝浣嗘槸锛屽�傛灉瀛樺湪缂洪櫡锛屽畠浠�搴斿緢灏戯紝鑰屼笖寰堥毦鍙戠幇銆傚�傛灉浣犻亣鍒伴棶棰橈紝濡傛灉浣犲皾璇曟壘鍑哄�艰嚧绯荤粺宕╂簝鐨勫噯纭�鍘熷洜锛岃繖濮嬬粓寰堟湁甯�鍔╋紝杩欐槸鍥犱负锛屽�傛灉杩欐牱鐨勮瘽锛屽揩閫熻В鍐抽棶棰樼殑鏈轰細寰堝ぇ銆�

棣栧厛锛屽簲灏濊瘯鎵惧嚭闂�棰樻槸鍚︿��mysqld鏈嶅姟鍣ㄦ湁鍏筹紝鎴栨槸鍚︿笌瀹㈡埛绔�鏈夊叧銆傞�氳繃鎵ц��mysqladmin version锛屽彲妫�鏌�mysqld鏈嶅姟鍣ㄨ繍琛屼簡澶氶暱鏃堕棿銆傚�傛��mysqld瀹曟満骞堕噸鍚�锛屽簲鏌ョ湅鏈嶅姟鍣ㄧ殑閿欒��鏃ュ織浠ユ壘鍑哄師鍥犮�傝�峰弬瑙�5.11.1鑺傦紝鈥滈敊璇�鏃ュ織鈥�銆�

鍦ㄦ煇浜涚郴缁熶笂锛屽湪閿欒��鏃ュ織涓�锛屽彲鍙戠��mysqld瀹曟満鐨勫爢鏍堣窡韪�锛屽彲浣跨��resolve_stack_dump绋嬪簭瑙ｅ喅瀹�銆傝�峰弬瑙�E.1.4鑺傦紝鈥滀娇鐢ㄥ爢鏍堣窡韪�鈥�銆傛敞鎰忥紝閿欒��鏃ュ織涓�鐨勫彉閲忓�煎苟闈炲�嬬粓鏄�100锛呮�ｇ‘鐨勩��

寰堝�氭湇鍔″櫒宕╂簝鏄�鍥犳崯鍧忕殑鏁版嵁鏂囦欢鎴栫储寮曟枃浠惰�屽�艰嚧鐨勶紝姣忔�℃墽琛屽畬SQL璇�鍙ヤ箣鍚庡苟鍦ㄥ悜瀹㈡埛绔�閫氬憡缁撴灉涔嬪墠锛�MySQL灏嗕娇鐢�write()绯荤粺璋冪敤鏇存柊纾佺洏涓婄殑鏂囦欢锛堝�傛灉浣犱娇鐢ㄤ簡鈥�--delay-key-write鈥濋�夐」锛屾儏鍐靛苟闈炲�傛�わ紝姝ゆ椂灏嗗啓鍏ユ暟鎹�鏂囦欢鑰屼笉鏄�绱㈠紩鏂囦欢锛夈�傝繖鎰忓懗鐫�锛屽嵆浣�mysqld宕╂簝锛屾暟鎹�鏂囦欢鐨勫唴瀹逛篃鏄�瀹夊叏鐨勶紝杩欐槸鍥犱负鎿嶄綔绯荤粺鑳戒繚璇佸皢鏈�鍒锋柊鐨勬暟鎹�鍐欏叆纾佺洏銆備娇鐢ㄢ��--flush鈥濋�夐」鍚�鍔�mysqld锛岃繖鏍凤紝姣忔�℃墽琛屽��SQL璇�鍙ュ悗锛屽彲寮哄��MySQL灏嗘墍鏈夊唴瀹瑰啓鍏ョ�佺洏銆�

鍓嶈堪浠嬬粛琛ㄦ槑锛屽湪姝ｅ父鎯呭喌涓嬩笉浼氬嚭鐜版崯鍧忕殑琛�锛岄櫎闈炲嚭鐜颁簡涓嬭堪鎯呭喌涔嬩竴锛�

鍦ㄦ洿鏂拌繃绋嬩腑锛�MySQL鏈嶅姟鍣ㄦ垨鏈嶅姟鍣ㄤ富鏈鸿��鍋滄��銆�

浣犲彂鐜颁簡mysqld涓�瀛樺湪鐨�1涓�缂洪櫡锛岃�ョ己闄峰�艰��mysqld鍦ㄦ洿鏂颁腑閫斾腑姝���

鍦�mysqld鎿嶄綔鐨勫悓鏃讹紝鏌愪簺澶栭儴绋嬪簭姝ｅ湪鎿嶆帶鏁版嵁鏂囦欢鎴栫储寮曟枃浠讹紝鏈�鎭板綋閿佸畾琛ㄣ��

浣犳�ｄ娇鐢ㄧ郴缁熶笂鐨勭浉鍚屾暟鎹�鐩�褰曡繍琛屽緢澶�mysqld鏈嶅姟鍣�锛岃�ョ郴缁熶笉鏀�鎸佽壇濂界殑鏂囦欢绯荤粺閿佸畾锛堥�氬父鏄�鐢�lockd閿佸畾绠＄悊鍣ㄨ礋璐ｇ殑锛夛紝鎴栦娇鐢ㄢ��--skip-external-locking鈥濋�夐」杩愯�屼簡澶氫釜鏈嶅姟鍣ㄣ��

宕╂簝鐨勬暟鎹�鏂囦欢鎴栫储寮曟枃浠讹紝鍏朵腑鍖呭惈瀵艰��mysqld娣蜂贡鐨勬崯鍧忓緢涓ラ噸鐨勬暟鎹�銆�

鍦ㄦ暟鎹�瀛樺偍鑺傜偣鍙戠幇缂洪櫡銆傝繖绉嶅彲鑳芥�т笉澶э紝浣嗚嚦灏戞槸鍙�鑳界殑銆傚湪璇ユ儏鍐典笅锛屽彲鍦ㄤ慨澶嶇殑琛ㄥ壇鏈�涓婏紝閫氳繃浣跨敤ALTER TABLE锛�灏濊瘯灏嗚〃绫诲瀷鏇存敼涓哄彟涓�绉嶅瓨鍌ㄥ紩鎿庛��

鐢变簬寰堥毦寰楃煡涓轰粈涔堟煇浜嬩細鍑虹幇宕╂簝锛岄�栧厛璇锋��鏌ョ敤浜庡叾浠栨柟闈㈢殑浜嬮」鏄�鍚﹀穿婧冦�傝�峰皾璇曢噰鍙栦笅杩版帾鏂斤��

鐢�mysqladmin shutdown鍋滄��mysqld鏈嶅姟鍣�锛屼粠鏁版嵁鐩�褰曡繍琛�myisamchk --silent --force */*.MYI锛屾��鏌ユ墍鏈夌殑MyISAM琛�锛屽苟閲嶅��mysqld銆傝繖鏍凤紝灏辫兘纭�淇濅粠骞插噣鐨勭姸鎬佽繍琛屾湇鍔″櫒銆傝�峰弬瑙�绗�5绔狅細鏁版嵁搴撶�＄��銆�

浣跨敤鈥�--log鈥濋�夐」鍚�鍔�mysqld锛屽苟鏍规嵁鍐欏叆鏃ュ織鐨勪俊鎭�纭�瀹氭槸鍚︽煇浜涚壒娈婄殑鏌ヨ�㈡潃姝讳簡鏈嶅姟鍣ㄣ�傜害95%鐨勭己闄蜂笌鐗瑰畾鐨勬煡璇㈡湁鍏炽�傛�ｅ父鎯呭喌涓嬶紝杩欐槸鏈嶅姟鍣ㄩ噸鍚�鍓嶆棩蹇楁枃浠朵腑鏈�澶熸暟涓�鏌ヨ��涓�鐨�1涓�銆傝�峰弬瑙�5.11.2鑺傦紝鈥滈�氱敤鏌ヨ�㈡棩蹇椻��銆傚�傛灉鑳藉�熺敤鐗规畩鏌ヨ�㈤噸澶嶆潃姝�MySQL锛屽嵆浣垮湪鍙戝嚭鏌ヨ�㈠墠妫�鏌ヤ簡鎵�鏈夎〃鐨勬儏鍐典笅涔熷悓鏍凤紝閭ｄ箞浣犲氨搴旇兘纭�瀹氱己闄凤紝骞跺簲鎻愪氦鍏充簬璇ョ己闄风殑缂洪櫡鎶ュ憡銆傝�峰弬瑙�1.7.1.3鑺傦紝鈥滃�備綍閫氭姤缂洪櫡鍜岄棶棰樷��銆�

灏濊瘯鎻愪緵涓�涓�娴嬭瘯鑼冧緥锛屾垜浠�搴旇兘鍒╃敤璇ヨ寖渚嬮噸澶嶉棶棰樸�傝�峰弬瑙�E.1.6鑺傦紝鈥滃�傛灉鍑虹幇琛ㄥ穿婧冿紝璇风敓鎴愭祴璇曟�堜緥鈥�銆�

璇峰湪mysql-test鐩�褰曚笅骞舵牴鎹�MySQL鍩哄噯杩涜�屾祴璇曘��璇峰弬瑙�27.1.2鑺傦紝鈥淢ySQL娴嬭瘯濂椾欢鈥�銆傚畠浠�鑳界浉褰撹壇濂藉湴娴嬭��MySQL銆備綘涔熷彲浠ヤ负鍩哄噯娴嬭瘯澧炲姞浠ｇ爜锛屼互妯℃嫙浣犵殑搴旂敤绋嬪簭銆傚熀鍑嗘祴璇曞彲鍦ㄦ簮鐮佸垎鍙戠増鐨�sql-bench鐩�褰曚笅鎵惧埌锛屽�逛簬浜岃繘鍒跺垎鍙戠増锛屽彲鍦�MySQL瀹夎�呯洰褰曚笅鐨�sql-bench鐩�褰曚笅鎵惧埌銆�

灏濊瘯浣跨敤fork_big.pl鑴氭湰锛堝畠浣嶄簬婧愮爜鍒嗗彂鐗堢殑娴嬭瘯鐩�褰曚笅锛夈��

濡傛灉浣犲皢MySQL閰嶇疆涓鸿皟璇曟ā寮忥紝濡傛灉鏌愪簨鍑洪敊锛屽彲鏇翠负瀹规槗鍦版悳闆嗗叧浜庡彲鑳介敊璇�鐨勪俊鎭�銆傚�傛灉灏�MySQL閰嶇疆涓鸿皟璇曟ā寮忥紝鍙�鐢熸��1涓�瀹夊叏鐨勫唴瀛樺垎閰嶇▼搴忥紝鍙�浣跨敤瀹冨彂鐜版煇浜涢敊璇�銆傛�ゅ�栵紝瀹冭繕鎻愪緵浜嗗緢澶氳緭鍑猴紝杩欑被杈撳嚭涓庡嚭鐜扮殑闂�棰樼浉鍏炽�傚湪configure涓婁娇鐢ㄢ��--with-debug鈥濇垨鈥�--with-debug=full鈥濋�夐」閲嶆柊閰嶇疆MySQL锛岀劧鍚庡啀缂栬瘧瀹冦�傝�峰弬瑙�E.1鑺傦紝鈥滆皟璇昅ySQL鏈嶅姟鍣ㄢ��銆�

纭�淇濅负浣犵殑鎿嶄綔绯荤粺搴旂敤浜嗘渶鏂扮殑琛ヤ竵銆�

瀵�mysqld浣跨敤鈥�--skip-external-locking鈥濋�夐」銆傚湪鏌愪簺绯荤粺涓婏紝lockd閿佸畾绠＄悊鍣ㄤ笉鑳芥�ｇ‘宸ヤ綔锛�鈥�--skip-external-locking鈥�閫夐」閫氱煡mysqld涓嶄娇鐢ㄥ�栭儴閿佸畾銆傦紙杩欐剰鍛崇潃锛屼綘涓嶈兘鍦ㄧ浉鍚岀殑鏁版嵁鐩�褰曚笂杩愯��2涓�mysqld鏈嶅姟鍣�锛屽�傛灉浣跨敤myisamchk锛屽繀椤昏皑鎱庛�傜劧鑰岋紝灏濊瘯灏嗚�ラ�夐」鐢ㄤ綔娴嬭瘯涔熸槸鏈夌泭鐨勶級銆�

褰�mysqld鐪嬩笂鍘绘�ｅ湪杩愯�屼絾骞舵湭鍝嶅簲鏃讹紝鏄�鍚﹁繍琛屼��mysqladmin -u root processlist锛熸煇浜涙椂鍊欙紝鍗充娇浣犺�や��mysqld澶勪簬闂茬疆鐘舵�佹椂锛屽疄闄呮儏鍐靛苟闈炲�傛�ゃ�傞棶棰樺彲鑳芥槸鍥犱负鎵�鏈夎繛鎺ュ潎宸蹭娇鐢�锛屾垨瀛樺湪鏌愪簺鍐呴儴閿佸畾闂�棰樸�傚嵆浣垮湪璇ユ儏鍐典笅锛�mysqladmin -u root processlist閫氬父鑳藉�熻繘琛岃繛鎺ワ紝骞惰兘鎻愪緵鍏充簬褰撳墠杩炴帴鏁颁互鍙婂叾鐘舵�佺殑鏈夌敤淇℃伅銆�

鍦ㄨ繍琛屽叾浠栨煡璇㈢殑鍚屾椂锛屽湪鍗曠嫭鐨勭獥鍙ｄ腑杩愯�屽懡浠�mysqladmin -i 5 status鎴�mysqladmin -i 5 -r status锛屼互鐢熸垚缁熻�′俊鎭�銆�

灏濊瘯閲囩敤涓嬭堪鏂规硶锛�

浠�gdb锛堟垨鍙︿竴涓�璋冭瘯鍣�锛夊惎鍔�mysqld銆傝�峰弬瑙�E.1.3鑺傦紝鈥滃湪gdb鐜�澧冧笅璋冭瘯mysqld鈥�銆�

杩愯�屾祴璇曡剼鏈�銆�

鍦�3涓�杈冧綆灞傞潰涓婅緭鍑�backtrace锛堝悜鍚庤窡韪�锛夊拰灞�閮ㄥ彉閲忋�傚湪gdb涓�锛屽��mysqld鍦�gdb鍐呭穿婧冩椂锛屽彲浣跨敤涓嬭堪鍛戒护瀹屾垚璇ヤ换鍔★細

backtrace

info local

up

info local

up

info local

浣跨敤gdb锛屼綘杩樿兘妫�鏌ヤ笌info绾跨▼鍏卞瓨鐨勭嚎绋嬶紝骞跺垏鎹㈣嚦鐗瑰畾鐨勭嚎绋�N锛屽叾涓�锛�N鏄�绾跨��ID銆�

灏濊瘯鐢�Perl鑴氭湰妯℃嫙浣犵殑搴旂敤绋嬪簭锛屽己鍒�MySQL宕╂簝鎴栬�屼负寮傚父銆�

鍙戦�佹�ｅ父鐨勭己闄锋姤鍛娿�傝�峰弬瑙�1.7.1.3鑺傦紝鈥滃�備綍閫氭姤缂洪櫡鍜岄棶棰樷��銆傚簲姣旈�氬父鐨勬姤鍛婃洿璇︾粏銆傜敱浜�MySQL鏄�涓哄緢澶氫汉鎻愪緵鏈嶅姟鐨勶紝瀹冨彲鑳藉洜浠呭瓨鍦ㄤ簬浣犵殑璁＄畻鏈轰笂鐨勬煇浜嬪穿婧冿紙渚嬪�傦紝涓庝綘鐨勭壒瀹氱郴缁熷簱鏈夊叧鐨勯敊璇�锛夈��

濡傛灉浣犻亣鍒颁笌鍖呭惈鍔ㄦ�侀暱搴﹁�岀殑琛ㄦ湁鍏崇殑闂�棰橈紝鑰屼笖浣犱粎浣跨敤VARCHAR鍒�锛堣�屼笉鏄�BLOB鎴�TEXT鍒楋級锛屽彲灏濊瘯鐢�ALTER TABLE灏嗘墍鏈�VARCHAR鍒楁洿鏀逛负CHAR鍒椼�傝繖鏍凤紝灏变細寮哄埗MySQL浣跨敤鍥哄畾澶у皬鐨勮�屻�傚浐瀹氬ぇ灏忕殑琛屽崰鐢ㄧ殑绌洪棿鐣ュ�氾紝浣嗗�规崯鍧忕殑瀹瑰繊搴︽洿楂樸��

鐩�鍓嶇殑鍔ㄦ�佽�屼唬鐮佸��MySQL AB宸蹭娇鐢ㄥ�氬勾锛屽緢灏戦亣鍒伴棶棰橈紝浣嗕粠鏈�璐ㄤ笂鐪嬶紝鍔ㄦ�侀暱搴﹁�屾洿鍊惧悜浜庡嚭鐜伴敊璇�锛屽洜姝わ紝涓嶅Θ灏濊瘯閲囩敤璇ョ瓥鐣ヤ互鏌ョ湅瀹冩槸鍚︽湁甯�鍔╋紝杩欎笉澶变负涓�涓�濂戒富鎰忋��

璇婃柇闂�棰樻椂涓嶈�佸皢浣犵殑鏈嶅姟鍣ㄧ‖浠舵帓闄ゅ湪澶栥�傛湁缂洪櫡鐨勭‖浠惰兘澶熷�艰嚧鏁版嵁鎹熷潖銆傚�圭‖浠惰繘琛屾晠闅滆瘖鏂�涓庢帓闄ゆ搷浣滄椂锛屽挨鍏跺簲娉ㄦ剰RAM鍜岀‖鐩橀┍鍔ㄥ櫒銆�

鍦ㄦ湰鑺備腑锛屼粙缁嶄簡MySQL鍝嶅簲纾佺洏婊￠敊璇�鐨勬柟寮忥紙濡傗�滆�惧�囦笂鏃犲墿浣欑┖闂粹�濓級锛屼互鍙婂搷搴旇秴閰嶉�濋敊璇�鐨勬柟寮忥紙濡傗�滃啓鍏ュけ璐モ�濇垨鈥滆揪鍒颁簡鐢ㄦ埛灞忚斀闄愬埗鈥濓級銆�

鏈�鑺備粙缁嶇殑鍐呭�逛笌鍐欏叆MyISAM琛ㄦ湁鍏炽�傚畠涔熼�傜敤浜庡啓鍏ヤ簩杩涘埗鏃ュ織鏂囦欢鍜屼簩杩涘埗绱㈠紩鏂囦欢锛屼絾瀵�鈥�row鈥濆拰鈥�record鈥濈殑搴旂敤搴旇��瑙嗕负鈥�event鈥濄��

鍑虹幇纾佺洏婊＄姸鍐垫椂锛�MySQL灏嗭細

姣忓垎閽熸��鏌ヤ竴娆★紝鏌ョ湅鏄�鍚︽湁瓒冲�熺┖闂村啓鍏ュ綋鍓嶈�屻�傚�傛灉鏈夎冻澶熺┖闂达紝灏嗙户缁�锛屽氨鍍忎粈涔堜篃鏈�鍙戠敓涓�鏍枫��

姣�10鍒嗛挓灏�1涓�鏉＄洰鍐欏叆鏃ュ織鏂囦欢锛屾彁閱掔�佺洏婊＄姸鍐点��

涓轰簡鍑忚交闂�棰橈紝鍙�閲囧彇涓嬭堪鎺�鏂斤��

瑕佹兂缁х画锛屼粎闇�鏈夎冻澶熺殑纾佺洏绌洪棿浠ユ彃鍏ユ墍鏈夎�板綍銆�

瑕佹兂鏀惧純绾跨▼锛屽繀椤讳娇鐢�mysqladmin kill銆備笅娆℃��鏌ョ�佺洏鏃跺皢鏀惧純绾跨▼锛�1鍒嗛挓锛夈��

鍏朵粬绾跨▼鍙�鑳戒細姝ｅ湪绛夊緟瀵艰嚧纾佺洏婊＄姸鍐电殑琛ㄣ�傚�傛灉鏈夋暟涓�鈥滃凡閿佸畾鈥濈殑绾跨▼锛屾潃姝绘�ｅ湪纾佺洏婊＄姸鍐典笅绛夊緟鐨勬煇涓�绾跨▼锛屼互渚垮厑璁稿叾浠栫嚎绋嬬户缁�銆�

瀵瑰墠杩拌�屼负鐨勪緥澶栨槸锛屽綋浣犱娇鐢�REPAIR TABLE鎴�OPTIMIZE TABLE鏃讹紝鎴栧綋绱㈠紩鏄�鍦�LOAD DATA INFILE鎴�ALTER TABLE璇�鍙ュ悗銆佸湪鎵规搷浣滀腑鍒涘缓鐨勩�傛墍鏈夎繖浜涜��鍙ヨ兘鍒涘缓澶х殑涓存椂鏂囦欢锛屽�傛灉淇濈暀杩欎簺鏂囦欢锛屼細瀵艰嚧绯荤粺鍏朵粬閮ㄥ垎鍑虹幇澶ч棶棰樸�傚�傛灉鍦�MySQL鎵ц�岃繖绫绘搷浣滅殑鍚屾椂纾佺洏宸叉弧锛屽畠灏嗗垹闄ゅぇ鐨勪复鏃舵枃浠讹紝骞跺皢琛ㄦ爣娉ㄤ负宕╂簝銆備絾瀵逛簬ALTER TABLE渚嬪�栵紝鏃ц〃淇濇寔涓嶅彉銆�

MySQL浣跨敤鐜�澧冨彉閲�TMPDIR鐨勫�间綔涓轰繚瀛樹复鏃舵枃浠剁殑鐩�褰曠殑璺�寰勫悕銆傚�傛灉鏈�璁剧疆TMPDIR锛�MySQL灏嗕娇鐢ㄧ郴缁熺殑榛樿�ゅ�硷紝閫氬父涓�/tmp銆�/var/tmp鎴�/usr/tmp銆傚�傛灉鍖呭惈涓存椂鏂囦欢鐩�褰曠殑鏂囦欢绯荤粺杩囧皬锛屽彲瀵�mysqld浣跨敤鈥�鈥攖mpdir鈥�閫夐」锛�鍦ㄥ叿鏈夎冻澶熺┖闂寸殑鏂囦欢绯荤粺鍐呮寚瀹�1涓�鐩�褰曘��

鍦�MySQL 5.1涓�锛屸��鈥攖mpdir鈥�閫夐」鍙�琚�璁剧疆涓烘暟涓�璺�寰勭殑鍒楄〃锛屼互寰�鐜�鏂瑰紡浣跨敤銆傚湪Unix骞冲彴涓婏紝璺�寰勭敤鍐掑彿瀛楃��鈥�:鈥�闅斿紑锛屽湪Windows銆�NetWare鍜�OS/2骞冲彴涓婏紝璺�寰勭敤鍒嗗彿瀛楃��鈥�;鈥�闅斿紑銆傛敞鎰忥紝涓轰簡鏈夋晥鍒嗗竷璐熻浇锛岃繖浜涜矾寰勫簲浣嶄簬涓嶅悓鐨勭墿鐞嗙�佺洏涓婏紝鑰屼笉鏄�浣嶄簬鐩稿悓纾佺洏鐨勪笉鍚屽垎鍖轰腑銆�

濡傛灉MySQL鏈嶅姟鍣ㄦ�ｄ綔涓哄�嶅埗浠庢湇鍔″櫒浣跨敤锛屼笉搴斿皢鈥�--tmpdir鈥�璁剧疆涓烘寚鍚戝熀浜庡唴瀛樼殑鏂囦欢绯荤粺鐨勭洰褰曪紝鎴栧綋鏈嶅姟鍣ㄤ富鏈洪噸鍚�鏃跺皢娓呯┖鐨勭洰褰曘�傚�逛簬澶嶅埗浠庢湇鍔″櫒锛�闇�瑕佸湪鏈哄櫒閲嶅惎鏃朵粛淇濈暀涓�浜涗复鏃舵枃浠讹紝浠ヤ究鑳藉�熷�嶅埗涓存椂琛ㄦ垨鎵ц��LOAD DATA INFILE鎿嶄綔銆傚�傛灉鍦ㄦ湇鍔″櫒閲嶅惎鏃朵涪澶变簡涓存椂鏂囦欢鐩�褰曚笅鐨勬枃浠讹紝澶嶅埗灏嗗け璐ャ��

MySQL浼氫互闅愬惈鏂瑰紡鍒涘缓鎵�鏈夌殑涓存椂鏂囦欢銆傝繖鏍凤紝灏辫兘纭�淇濅腑姝�mysqld鏃朵細鍒犻櫎鎵�鏈変复鏃舵枃浠躲�備娇鐢ㄩ殣鍚�鏂囦欢鐨勭己鐐瑰湪浜庯紝鍦ㄤ复鏃舵枃浠剁洰褰曟墍鍦ㄧ殑浣嶇疆涓�锛岀湅涓嶅埌鍗犵敤浜嗘枃浠剁郴缁熺殑澶т复鏃舵枃浠躲��

杩涜�屾帓搴忔椂锛�ORDER BY鎴�GROUP BY锛夛紝MySQL閫氬父浼氫娇鐢�1涓�鎴栧�氫釜涓存椂鏂囦欢銆傛墍闇�鐨勬渶澶х�佺洏绌洪棿鐢变笅杩拌〃杈惧紡鍐冲畾锛�

(length of what is sorted + sizeof(row pointer))

* number of matched rows

* 2

鈥�row pointer鈥濓紙琛屾寚閽堬級鐨勫ぇ灏忛�氬父鏄�4瀛楄妭锛屼絾鍦ㄤ互鍚庯紝瀵逛簬澶х殑琛�锛岃�ュ�煎彲鑳戒細澧炲姞銆�

瀵逛簬鏌愪簺SELECT鏌ヨ��锛�MySQL杩樹細鍒涘缓涓存椂SQL琛ㄣ�傚畠浠�涓嶆槸闅愬惈琛�锛屽苟鍏锋湁SQL_*褰㈠紡鐨勫悕绉般��

ALTER TABLE浼氬湪涓庡師濮嬭〃鐩�褰曠浉鍚岀殑鐩�褰曚笅鍒涘缓涓存椂琛ㄣ��

瀵逛簬鏈嶅姟鍣ㄧ敤鏉ヤ笌鏈�鍦板�㈡埛绔�杩涜�岄�氫俊鐨�Unix濂楁帴瀛楁枃浠讹紝鍏堕粯璁や綅缃�鏄�/tmp/mysql.sock銆傝繖鏈夊彲鑳藉�艰嚧闂�棰橈紝鍘熷洜鍦ㄤ簬锛屽湪鏌愪簺鐗堟湰鐨�Unix涓婏紝浠讳綍浜洪兘鑳藉垹闄�/tmp鐩�褰曚笅鐨勬枃浠躲��

鍦ㄥぇ澶氭暟Unix鐗堟湰涓�锛屽彲瀵�/tmp鐩�褰曡繘琛屼繚鎶わ紝浣垮緱鏂囦欢鍙�鑳借��鍏舵墍鏈夎繖鎴栬秴绾х敤鎴凤紙鏍圭敤鎴凤級鍒犻櫎銆備负姝わ紝浠ユ牴鐢ㄦ埛韬�浠界櫥褰曪紝骞朵娇鐢ㄤ笅杩板懡浠ゅ��/tmp鐩�褰曚笂璁剧疆绮樼潃浣嶏細

shell> chmod +t /tmp

閫氳繃鎵ц��ls -ld /tmp锛屽彲妫�鏌ユ槸鍚﹁�剧疆浜嗙矘鐫�浣嶃�傚�傛灉鏈�鍚庝竴涓�璁稿彲瀛楃�︽槸鈥渢鈥�锛岃〃鏄庤�剧疆浜嗙矘鐫�浣嶃��

鍙︿竴绉嶆柟娉曟槸鏀瑰彉鏈嶅姟鍣ㄥ垱寤�Unix濂楁帴瀛楁枃浠剁殑浣嶇疆銆傚�傛灉杩涜�屼簡杩欑被鎿嶄綔锛岃繕搴旇�╁�㈡埛绔�绋嬪簭鐭ラ亾鏂囦欢鐨勪綅缃�銆傝兘澶熶互澶氱�嶄笉鍚屾柟寮忔寚瀹氭枃浠朵綅缃�锛�

鍦ㄥ叏灞�鎴栧眬閮ㄩ�夐」鏂囦欢涓�鎸囧畾璺�寰勩�備緥濡傦紝灏嗕笅杩拌�岀疆浜庢枃浠�/etc/my.cnf涓�锛�

[mysqld]

socket=/path/to/socket

[client]

socket=/path/to/socket

璇峰弬瑙�4.3.2鑺傦紝鈥滀娇鐢ㄩ�夐」鏂囦欢鈥�銆�

鍦ㄨ繍琛屽�㈡埛绔�绋嬪簭鏃讹紝鍦ㄥ懡浠よ�屼笂涓�mysqld_safe鎸囧畾鈥�--socket鈥�閫夐」銆�

灏�MYSQL_UNIX_PORT鐜�澧冨彉閲忚�剧疆涓�Unix濂楁帴瀛楁枃浠剁殑璺�寰勩��

閲嶆柊浠庢簮鐮佺紪璇�MySQL锛屼互浣跨敤涓嶅悓鐨勯粯璁�Unix濂楁帴瀛楁枃浠朵綅缃�銆傝繍琛�configure鏃讹紝鐢ㄢ��--with-unix-socket-path鈥濋�夐」瀹氫箟鏂囦欢璺�寰勩�傝�峰弬瑙�2.8.2鑺傦紝鈥滃吀鍨嬮厤缃�閫夐」鈥�銆�

鐢ㄤ笅杩板懡浠よ繛鎺ユ湇鍔″櫒锛岃兘澶熸祴璇曟柊鐨勫�楁帴瀛椾綅缃�鏄�鍚﹀伐浣滐��

shell> mysqladmin --socket=/path/to/socket version

濡傛灉閬囧埌涓�SELECT NOW()鏈夊叧鐨勯棶棰橈紝瀹冭繑鍥�GMT鍊艰�屼笉鏄�褰撳湴鏃堕棿锛屽氨搴旈�氱煡鏈嶅姟鍣ㄤ綘鐨勫綋鍓嶅け鍘汇�傚�傛��UNIX_TIMESTAMP()杩斿洖閿欒��鍊硷紝涓婅堪鏂瑰紡鍚屾牱閫傜敤銆傚簲涓烘湇鍔″櫒鎵�杩愯�岀殑鐜�澧冭繘琛岃繖绫昏�剧疆锛屼緥濡傦紝鍦�mysqld_safe鎴�mysql.server涓�銆傝�峰弬瑙�闄勫綍F锛�鐜�澧冨彉閲�銆�

涔熷彲浠ュ��mysqld_safe浣跨敤鈥�--timezone=timezone_name鈥濋�夐」锛屼负鏈嶅姟鍣ㄨ�剧疆澶卞幓銆備篃鍙�浠ュ湪鍚�鍔�mysqld涔嬪墠锛岄�氳繃璁剧疆TZ鐜�澧冨彉閲忓畬鎴愯�ヨ�剧疆銆�

鈥�--timezone鈥濇垨TZ鐨勫厑璁稿�间笌绯荤粺鏈夊叧銆傚叧浜庡彲鎺ュ彈鐨勫�硷紝璇峰弬瑙佹搷浣滅郴缁熸枃妗ｃ��

鍦ㄩ粯璁ゆ儏鍐典笅锛�MySQL鎼滅储涓嶅尯鍒嗗ぇ灏忓啓锛堜絾鏌愪簺瀛楃�﹂泦濮嬬粓鍖哄垎澶у皬鍐欙紝濡�czech锛夈�傝繖鎰忓懗鐫�锛屽�傛灉浣犱娇鐢�col_name LIKE 'a%'杩涜�屾悳绱�锛屼綘灏嗚幏寰椾互A鎴�a寮�濮嬬殑鎵�鏈夊垪銆傚�傛灉鎵撶畻浣挎悳绱㈠尯鍒嗗ぇ灏忓啓锛�璇风‘淇濇搷浣滄暟涔嬩竴鍏锋湁鍖哄垎澶у皬鍐欑殑鎴栦簩杩涘埗鏍″�广�備緥濡傦紝濡傛灉浣犳�ｅ湪姣旇緝鍧囬�傜敤latin1瀛楃�﹂泦鐨勫垪鍜屽瓧绗︿覆锛屽彲浣跨��COLLATE鎿嶄綔绗�锛屼娇1涓�鎿嶄綔鏁板叿鏈�latin1_general_cs鎴�latin1_bin鏍″��鐗规�с�備緥濡傦細

col_name COLLATE latin1_general_cs LIKE 'a%'

col_name LIKE 'a%' COLLATE latin1_general_cs

col_name COLLATE latin1_bin LIKE 'a%'

col_name LIKE 'a%' COLLATE latin1_bin

濡傛灉甯屾湜鎬绘槸浠ュ尯鍒嗗ぇ灏忓啓鐨勬柟寮忓�勭悊鍒楋紝鍙�浣跨敤鍖哄垎澶у皬鍐欑殑鎴栦簩杩涘埗鏍″�瑰０鏄庡畠銆傝�峰弬瑙�13.1.5鑺傦紝鈥淐REATE TABLE璇�娉曗��銆�

绠�鍗曠殑姣旇緝鎿嶄綔锛�>=, >, =, <, <=, 鎺掑簭鍜屽垎缁勶級鍩轰簬姣忎釜瀛楃�︾殑鈥滄帓搴忓�尖�濄�傚叿鏈夌浉鍚屾帓搴忓�肩殑瀛楃�︼紙濡傗��E鈥�, 鈥�e鈥�,鍜屸��脙漏鈥欙級灏嗚��褰撲綔鐩稿悓鐨勫啓瀛楃�︺��

DATE鍊肩殑鏍煎紡鏄�'YYYY-MM-DD'銆�鎸夌収鏍囧噯鐨�SQL锛屼笉鍏佽�稿叾浠栨牸寮忋�傚湪UPDATE琛ㄨ揪寮忎互鍙�SELECT璇�鍙ョ��WHERE瀛愬彞涓�搴斾娇鐢ㄨ�ユ牸寮忋�備緥濡傦細

mysql> SELECT * FROM tbl_name WHERE date >= '2003-05-05';

涓轰簡鏂逛究锛屽�傛灉鏃ユ湡鏄�鍦ㄦ暟鍊肩幆澧冧笅浣跨敤鐨勶紝MySQL浼氳嚜鍔ㄥ皢鏃ユ湡杞�鎹�涓烘暟鍊硷紙鍙嶄箣浜︾劧锛夈�傚畠杩樺叿鏈夌浉褰撶殑鏅鸿兘锛屽湪鏇存柊鏃舵垨鍦ㄤ笌TIMESTAMP銆�DATE鎴�DATETIME鍒楁瘮杈冩棩鏈熺殑WHERE瀛愬彞涓�锛屽厑璁�鈥�瀹芥澗鐨�鈥�瀛楃�︿覆褰㈠紡锛�鈥�瀹芥澗褰㈠紡鈥�琛ㄧず锛屼换浣曟爣鐐瑰瓧绗﹀潎鑳界敤浣滃悇閮ㄥ垎涔嬮棿鐨勫垎闅旂�︺�備緥濡傦紝'2004-08-15'鍜�'2004#08#15'鏄�绛夊悓鐨勶級銆�MySQL杩樿兘杞�鎹�涓嶅惈浠讳綍鍒嗛殧绗︾殑瀛楃�︿覆锛堝��'20040815'锛夛紝鍓嶄綋鏄�瀹冨繀椤绘槸鏈夋剰涔夌殑鏃ユ湡銆�

浣跨敤<銆�<=銆�=銆�>=銆�>銆佹垨BETWEEN鎿嶄綔绗﹀皢DATE銆�TIME銆�DATETIME鎴�TIMESTAMP涓庡父閲忓瓧绗︿覆杩涜�屾瘮杈冩椂锛�MySQL閫氬父浼氬皢瀛楃�︿覆杞�鎹�涓哄唴閮ㄩ暱鏁存暟锛屼互渚胯繘琛屽揩閫熸瘮杈冿紙浠ュ強鐣ヤ负鈥滃�芥澗鈥濈殑瀛楃�︿覆妫�鏌ワ級銆備絾鏄�锛岃�ヨ浆鎹㈠叿鏈変笅杩颁緥澶栵細

姣旇緝涓ゅ垪鏃�

灏�DATE銆�TIME銆�DATETIME鎴�TIMESTAMP鍒椾笌琛ㄨ揪寮忚繘琛屾瘮杈冩椂

浣跨敤鍏朵粬姣旇緝鏂规硶鏃讹紝濡�IN鎴�STRCMP()銆�

瀵逛簬杩欎簺渚嬪�栨儏褰�锛屼細灏嗗�硅薄杞�鎹�涓哄瓧绗︿覆骞舵墽琛屽瓧绗︿覆姣旇緝锛岄噰鐢ㄨ�ユ柟寮忚繘琛屾瘮杈冦��

涓轰簡淇濇寔瀹夊叏锛屽亣瀹氭寜瀛楃�︿覆姣旇緝瀛楃�︿覆锛屽�傛灉浣犳墦绠楁瘮杈冧复鏃跺�煎拰瀛楃�︿覆锛屽皢浣跨敤鎭板綋鐨勫瓧绗︿覆鍑芥暟銆�

瀵逛簬鐗规畩鏃ユ湡'0000-00-00'锛岃兘澶熶互'0000-00-00'褰㈠紡淇濆瓨鍜屾��绱��傚湪MyODBC涓�浣跨��'0000-00-00'鏃ユ湡鏃讹紝瀵逛簬MyODBC 2.50.12鎴栨洿楂樼増鏈�锛岃�ユ棩鏈熷皢琚�鑷�鍔ㄨ浆鎹�涓�NULL锛岃繖鏄�鍥犱��ODBC涓嶈兘澶勭悊杩欑被鏃ユ湡銆�

鐢变簬MySQL鑳藉�熸墽琛屽墠闈㈡墍浠嬬粛鐨勮浆鎹�锛屼笅杩拌��鍙ュ潎鑳芥�ｅ父宸ヤ綔锛�

mysql> INSERT INTO tbl_name (idate) VALUES (19970505);

mysql> INSERT INTO tbl_name (idate) VALUES ('19970505');

mysql> INSERT INTO tbl_name (idate) VALUES ('97-05-05');

mysql> INSERT INTO tbl_name (idate) VALUES ('1997.05.05');

mysql> INSERT INTO tbl_name (idate) VALUES ('1997 05 05');

mysql> INSERT INTO tbl_name (idate) VALUES ('0000-00-00');

mysql> SELECT idate FROM tbl_name WHERE idate >= '1997-05-05';

mysql> SELECT idate FROM tbl_name WHERE idate >= 19970505;

mysql> SELECT MOD(idate,100) FROM tbl_name WHERE idate >= 19970505;

mysql> SELECT idate FROM tbl_name WHERE idate >= '19970505';

浣嗘槸锛屼笅杩拌��鍙ヤ笉鑳芥�ｅ父宸ヤ綔锛�

mysql> SELECT idate FROM tbl_name WHERE STRCMP(idate,'20030505')=0;

STRCMP()鏄�涓�绉嶅瓧绗︿覆鍑芥暟锛屽畠鑳藉皢idate杞�鎹�涓�'YYYY-MM-DD'鏍煎紡鐨勫瓧绗︿覆锛�骞舵墽琛屽瓧绗︿覆姣旇緝銆傚畠涓嶈兘灏�'20030505'杞�鎹�涓�鏃ユ湡'2003-05-05'骞惰繘琛屾棩鏈熸瘮杈冦��

濡傛灉浣犳�ｅ湪浣跨��ALLOW_INVALID_DATES SQL妯″紡锛�MySQL鍏佽�镐互浠呮墽琛岀粰瀹氱殑鏈夐檺妫�鏌ユ柟寮忎繚瀛樻棩鏈燂細MySQL浠呬繚璇佸ぉ浣嶄簬1锝�31鐨勮寖鍥村唴锛屾湀浣嶄簬1锝�12鐨勮寖鍥村唴銆�

杩欐牱灏变娇寰�MySQL寰堥�傚悎浜�Web搴旂敤绋嬪簭锛屽叾涓�锛屼綘鑳借幏寰椾笁涓�涓嶅悓瀛楁�典腑鐨勫勾銆佹湀銆佹棩鍊硷紝涔熻兘鍑嗙‘淇濆瓨鐢ㄦ埛鎻掑叆鐨勫�硷紙鏃犳棩鏈熼獙璇侊級銆�

濡傛灉鏈�浣跨��NO_ZERO_IN_DATE SQL妯″紡锛屸�滃ぉ鈥濆拰鈥滄湀鈥濋儴鍒嗗彲鑳戒负0銆傚�傛灉浣犳墦绠楀皢鐢熸棩淇濆瓨鍦�DATE鍒楄�屼笖浠呯煡閬撻儴鍒嗘棩鏈燂紝瀹冨崄鍒嗘柟渚裤��

濡傛灉鏈�浣跨��NO_ZERO_DATE SQL妯″紡锛�MySQL涔熷厑璁镐綘灏�'0000-00-00'淇濆瓨涓衡�滀吉鏃ユ湡鈥濄�傚湪鏌愪簺鎯呭喌涓嬶紝瀹冩瘮浣跨敤NULL鍊兼洿鏂逛究銆�

濡傛灉鏃犳硶灏嗘棩鏈熻浆鎹�涓轰换浣曞悎鐞嗗�硷紝鈥�0鈥濆皢淇濆瓨鍦�DATE鍒椾腑锛屽苟琚�妫�绱�涓�'0000-00-00'銆傝繖鏄�鍏奸【閫熷害鍜屼究鍒╂�х殑浜嬪疁銆傛垜浠�璁や负锛屾暟鎹�搴撴湇鍔″櫒鐨勮亴璐ｆ槸妫�绱�涓庝綘淇濆瓨鐨勬棩鏈熺浉鍚岀殑鏃ユ��锛堝嵆浣垮湪浠讳綍鎯呭喌涓嬶紝鏁版嵁鍦ㄩ�昏緫涓婁笉姝ｇ‘涔熷悓鏍凤級銆傛垜浠�璁や负锛屽�规棩鏈熺殑妫�鏌ュ簲鐢卞簲鐢ㄧ▼搴忚�屼笉鏄�鏈嶅姟鍣ㄨ礋璐ｃ��

濡傛灉浣犲笇鏈�MySQL妫�鏌ユ墍鏈夋棩鏈熷苟浠呮帴鍙楀悎娉曟棩鏈燂紙闄ら潪鐢�IGNORE瑕嗙洊锛夛紝搴斿皢sql_mode璁剧疆涓�"NO_ZERO_IN_DATE,NO_ZERO_DATE"銆�

瀵逛簬SQL鐨勬柊鎵嬶紝NULL鍊肩殑姒傚康甯稿父浼氶�犳垚娣锋穯锛屼粬浠�甯歌�や负NULL鏄�涓庣┖瀛楃�︿覆''鐩稿悓鐨勪簨銆�鎯呭喌骞堕潪濡傛�ゃ�備緥濡傦紝涓嬭堪璇�鍙ユ槸瀹屽叏涓嶅悓鐨勶��

mysql> INSERT INTO my_table (phone) VALUES (NULL);

mysql> INSERT INTO my_table (phone) VALUES ('');

杩欎袱鏉¤��鍙ュ潎浼氬皢鍊兼彃鍏�phone锛堢數璇濓級鍒楋紝浣嗙��1鏉¤��鍙ユ彃鍏ョ殑鏄�NULL鍊硷紝绗�2鏉¤��鍙ユ彃鍏ョ殑鏄�绌哄瓧绗︿覆銆傜��1绉嶆儏鍐电殑鍚�涔夊彲琚�瑙ｉ噴涓衡�滅數璇濆彿鐮佹湭鐭モ�濓紝鑰岀��2绉嶆儏鍐电殑鍚�涔夊彲琚�瑙ｉ噴涓衡�滆�ヤ汉鍛樻病鏈夌數璇濓紝鍥犳�ゆ病鏈夌數璇濆彿鐮佲�濄��

涓轰簡杩涜��NULL澶勭悊锛屽彲浣跨敤IS NULL鍜�IS NOT NULL鎿嶄綔绗︿互鍙�IFNULL()鍑芥暟銆�

鍦�SQL涓�锛�NULL鍊�涓庝换浣曞叾瀹冨�肩殑姣旇緝锛堝嵆浣挎槸NULL锛夋案杩滀笉浼氫负鈥滅湡鈥濄�傚寘鍚�NULL鐨勮〃杈惧紡鎬绘槸浼氬�煎��NULL鍊硷紝闄ら潪鍦ㄥ叧浜庢搷浣滅�︾殑鏂囨。涓�浠ュ強琛ㄨ揪寮忕殑鍑芥暟涓�浣滀簡鍏朵粬瑙勫畾銆備笅杩扮ず渚嬩腑鐨勬墍鏈夊垪鍧囪繑鍥�NULL锛�

mysql> SELECT NULL, 1+NULL, CONCAT('Invisible',NULL);

濡傛灉鎵撶畻鎼滅储鍒楀�间负NULL鐨勫垪锛屼笉鑳戒娇鐢�expr = NULL娴嬭瘯銆備笅杩拌��鍙ヤ笉杩斿洖浠讳綍琛岋紝杩欐槸鍥犱负锛屽�逛簬浠讳綍琛ㄨ揪寮忥��expr = NULL姘歌繙涓嶄负鈥�鐪�鈥�锛�

mysql> SELECT * FROM my_table WHERE phone = NULL;

瑕佹兂鏌ユ壘NULL鍊硷紝蹇呴』浣跨敤IS NULL娴嬭瘯銆�鍦ㄤ笅闈㈢殑璇�鍙ヤ腑锛屼粙缁嶄簡鏌ユ��NULL鐢佃瘽鍙风爜鍜岀┖鐢佃瘽鍙风爜鐨勬柟寮忥細

mysql> SELECT * FROM my_table WHERE phone IS NULL;

mysql> SELECT * FROM my_table WHERE phone = '';

鏇村�氫俊鎭�鍜岀ず渚嬶紝璇峰弬瑙�3.3.4.6鑺傦紝鈥滀娇鐢∟ULL鍊尖��銆�

濡傛灉浣犳�ｅ湪浣跨��MyISAM銆�InnoDB銆�BDB銆佹垨MEMORY瀛樺偍寮曟搸锛岃兘澶熷湪鍙�鑳藉叿鏈�NULL鍊肩殑鍒椾笂澧炲姞1鏉＄储寮曘�傚�備笉鐒讹紝蹇呴』澹版槑绱㈠紩鍒椾��NOT NULL锛岃�屼笖涓嶈兘灏�NULL鎻掑叆鍒板垪涓�銆�

鐢�LOAD DATA INFILE璇诲彇鏁版嵁鏃讹紝瀵逛簬绌虹殑鎴栦涪澶辩殑鍒楋紝灏嗙敤''鏇存柊瀹冧滑銆傚�傛灉甯屾湜鍦ㄥ垪涓�鍏锋湁NULL鍊硷紝搴斿湪鏁版嵁鏂囦欢涓�浣跨��\N銆傚湪鏌愪簺鎯呭喌涓嬶紝涔熷彲浠ヤ娇鐢ㄦ枃瀛楁�у崟璇嶁��NULL鈥濄�傝�峰弬瑙�13.2.5 鈥�LOAD DATA INFILE璇�娉曗��銆�

浣跨敤DISTINCT銆�GROUP BY鎴�ORDER BY鏃讹紝鎵�鏈�NULL鍊煎皢琚�瑙嗕负绛夊悓鐨勩��

浣跨敤ORDER BY鏃讹紝棣栧厛灏嗘樉绀�NULL鍊硷紝濡傛灉鎸囧畾浜�DESC鎸夐檷搴忔帓鍒楋紝NULL鍊煎皢鏈�鍚庢樉绀恒��

瀵逛簬鑱氬悎锛堢疮璁★級鍑芥暟锛屽��COUNT()銆�MIN()鍜�SUM()锛屽皢蹇界暐NULL鍊笺��瀵规�ょ殑渚嬪�栨槸COUNT(*)锛屽畠灏嗚�℃暟琛岃�屼笉鏄�鍗曠嫭鐨�鍒楀�笺�備緥濡傦紝涓嬭堪璇�鍙ヤ骇鐢熶袱涓�璁℃暟銆傞�栧厛璁℃暟琛ㄤ腑鐨勮�屾暟锛屽叾娆¤�℃��age鍒椾腑鐨勯潪NULL鍊兼暟鐩�锛�

mysql> SELECT COUNT(*), COUNT(age) FROM person;

瀵逛簬鏌愪簺鍒楃被鍨嬶紝MySQL灏嗗��NULL鍊艰繘琛岀壒娈婂�勭悊銆傚�傛灉灏�NULL鎻掑叆TIMESTAMP鍒楋紝灏嗘彃鍏ュ綋鍓嶆棩鏈熷拰鏃堕棿銆傚�傛灉灏�NULL鎻掑叆鍏锋湁AUTO_INCREMENT灞炴�х殑鏁存暟鍒楋紝灏嗘彃鍏ュ簭鍒椾腑鐨勪笅涓�涓�缂栧彿銆�

SELECT SQRT(a*b) AS root FROM tbl_name GROUP BY root HAVING root > 0;

SELECT id, COUNT(*) AS cnt FROM tbl_name GROUP BY id HAVING cnt > 0;

SELECT id AS 'Customer identity' FROM tbl_name;

SELECT id, COUNT(*) AS cnt FROM tbl_name WHERE cnt > 0 GROUP BY id;

鎵ц��ROLLBACK锛堝洖婊氾級鏃讹紝濡傛灉鏀跺埌涓嬭堪娑堟伅锛岃〃绀轰簨鍔′腑浣跨敤鐨�1涓�鎴栧�氫釜琛ㄤ笉鏀�鎸佷簨鍔★��

璀﹀憡锛氭煇浜涙洿鏀圭殑闈炰簨鍔℃�ц〃涓嶈兘琚�鍥炴粴銆�

杩欎簺闈炰簨鍔℃�ц〃涓嶅彈ROLLBACK璇�鍙ョ殑褰卞搷銆�

濡傛灉鍦ㄤ簨鍔′腑鎰忓�栧湴娣峰悎浜嗕簨鍔℃�ц〃鍜岄潪浜嬪姟鎬ц〃锛屽�艰嚧璇ユ秷鎭�鐨勬渶鍙�鑳藉師鍥犳槸锛屼綘璁や负鏈�搴旀槸浜嬪姟鎬х殑琛ㄥ疄闄呬笂涓嶆槸銆傚�備綘璇曞浘浣跨��mysqld鏈嶅姟鍣ㄤ笉鏀�鎸佺殑浜嬪姟鎬у瓨鍌ㄥ紩鎿庯紙鎴栫敤鍚�鍔ㄩ�夐」绂佹��浜嗗畠锛夊垱寤鸿〃锛屽氨鍙�鑳藉嚭鐜拌�ユ儏鍐点�傚�傛��mysqld涓嶆敮鎸佸瓨鍌ㄥ紩鎿庯紝瀹冨皢浠�MyISAM琛ㄥ垱寤鸿〃锛岃繖鏄�闈炰簨鍔℃�ц〃銆�

鍙�浣跨敤涓嬭堪璇�鍙ヤ箣涓�妫�鏌ヨ〃鐨勬爣绫诲瀷锛�

SHOW TABLE STATUS LIKE 'tbl_name';

SHOW CREATE TABLE tbl_name;

璇峰弬瑙�13.5.4.18鑺傦紝鈥淪HOW TABLE STATUS璇�娉�浠ュ強13.5.4.5鑺傦紝鈥淪HOW CREATE TABLE璇�娉曗��銆�

浣跨敤涓嬭堪璇�鍙ワ紝鍙�妫�鏌�mysqld鏈嶅姟鍣ㄦ敮鎸佺殑瀛樺偍寮曟搸锛�

SHOW ENGINES;

涔熷彲浠ヤ娇鐢ㄤ笅杩拌��鍙ワ紝妫�鏌ヤ笌浣犳劅鍏磋叮鐨勫瓨鍌ㄥ紩鎿庢湁鍏崇殑鍙橀噺鍊硷細

SHOW VARIABLES LIKE 'have_%';

渚嬪�傦紝瑕佹兂纭�瀹�InnoDB瀛樺偍寮曟搸鏄�鍚﹀彲鐢�锛屽彲妫�鏌�have_innodb鍙橀噺鐨勫�笺��

璇峰弬瑙�13.5.4.8鑺傦紝鈥淪HOW ENGINES璇�娉曗��鍜�13.5.4.21鑺傦紝鈥淪HOW VARIABLES璇�娉曗��銆�

濡傛灉鏈変娇鐢ㄤ簡寰堝�氳〃鐨勫�嶆潅鏌ヨ��锛屼絾鏈�杩斿洖浠讳綍琛岋紝搴旈噰鐢ㄤ笅杩版�ラ�ゆ壘鍑轰粈涔堝嚭閿欙細

鐢�EXPLAIN娴嬭瘯鏌ヨ��锛屼互妫�鏌�鏄�鍚﹀彂鐜版煇浜嬫樉鐒跺嚭閿欍�傝�峰弬瑙�7.2.1鑺傦紝鈥淓XPLAIN璇�娉曪紙鑾峰彇鍏充簬SELECT鐨勪俊鎭�锛�鈥�銆�

浠呴�夋嫨鍦�WHERE瀛愬彞涓�浣跨敤鐨勫垪銆�

浠庢煡璇�涓�1娆″垹闄�1涓�琛�锛岀洿鑷宠繑鍥炰簡鏌愪簺琛屼负姝��傚�傛灉琛ㄥ緢澶э紝杈冨ソ鐨勪富鎰忔槸鍦ㄦ煡璇�涓�浣跨��LIMIT 10銆�

瀵逛簬鍏锋湁涓庝笂娆′粠鏌ヨ��涓�鍒犻櫎鐨勮〃鍖归厤鐨勮�岀殑鍒楋紝鍙戝嚭SELECT鏌ヨ��銆�

濡傛灉灏�FLOAT鎴�DOUBLE鍒椾笌鍏锋湁鏁板�肩被鍨嬬殑鏁板�艰繘琛屾瘮杈冿紝涓嶈兘浣跨敤绛夊紡(=)姣旇緝銆傚湪澶у�氭暟璁＄畻鏈鸿��瑷�涓�锛岃�ラ棶棰樺緢甯歌�侊紝杩欐槸鍥犱负锛屽苟闈炴墍鏈夌殑娴�鐐瑰�煎潎鑳戒互鍑嗙‘鐨勭簿搴︿繚瀛樸�傚湪鏌愪簺鎯呭喌涓嬶紝灏�FLOAT鏇存敼涓�DOUBLE鍙�鏇存�ｈ�ラ棶棰樸��璇峰弬瑙�A.5.8鑺傦紝鈥滀笌娴�鐐规瘮杈冩湁鍏崇殑闂�棰樷��銆�

濡傛灉浠嶄笉鑳芥壘鍑洪棶棰樹箣鎵�鍦�锛岃�峰垱寤鸿兘涓庢樉绀洪棶棰樼殑鈥�mysql test < query.sql鈥濅竴璧疯繍琛岀殑鏈�灏忔祴璇曘�傞�氳繃浣跨敤mysqldump --quick db_name tbl_name_1 ... tbl_name_n > query.sql杞�鍌ㄨ〃锛屽彲鍒涘缓娴嬭瘯鏂囦欢銆傚湪缂栬緫鍣ㄤ腑鎵撳紑鏂囦欢锛屽垹闄ゆ煇浜涙彃鍏ョ殑琛岋紙濡傛灉鏈夎秴鍑烘紨绀洪棶棰樻墍闇�鐨勮�岋級锛屽苟鍦ㄦ枃浠舵湯灏炬坊鍔�SELECT璇�鍙ャ��

閫氳繃鎵ц�屼笅杩板懡浠わ紝楠岃瘉娴嬭瘯鏂囦欢鑳芥紨绀洪棶棰橈��

shell> mysqladmin create test2

shell> mysql test2 < query.sql

浣跨敤mysqlbug灏嗘祴璇曟枃浠跺紶璐村埌鍝熷�滈�氱敤MySQL閭�浠跺垪琛ㄣ�傝�峰弬瑙�1.7.1.1鑺傦紝鈥淭he MySQL閭�浠跺垪琛ㄢ��銆�

ALTER TABLE灏嗚〃鏇存敼涓哄綋鍓嶅瓧绗﹂泦銆傚�傛灉鍦ㄦ墽琛�ALTER TABLE鎿嶄綔鏈熼棿閬囧埌閲嶅�嶉敭閿欒��锛屽師鍥犲湪浜庢柊鐨勫瓧绗﹂泦灏�2涓�閿�鏄犲皠鍒颁簡鐩稿悓鍊硷紝鎴栨槸琛ㄥ凡鎹熷潖銆傚湪鍚庝竴绉嶆儏鍐典笅锛屽簲鍦ㄨ〃涓婅繍琛�REPAIR TABLE銆�

濡傛灉ALTER TABLE澶辫触骞剁粰鍑轰笅杩伴敊璇�锛岄棶棰樺彲鑳芥槸鍥犱负鍦�ALTER TABLE鎿嶄綔鐨勬棭鏈熼樁娈靛嚭鐜�MySQL宕╂簝锛屾病鏈夊悕涓�A-xxx鎴�B-xxx鐨勬棫琛�锛�

Error on rename of './database/name.frm'

to './database/B-xxx.frm' (Errcode: 17)

鍦ㄨ�ユ儏鍐典笅锛岃繘鍏�MySQL鏁版嵁鐩�褰曪紝骞跺垹闄ゅ叾鍚嶇О涓轰��A-鎴�B-寮�濮嬬殑鎵�鏈夋枃浠�锛堟垨璁镐綘甯屾湜灏嗗畠浠�绉诲姩鍒板叾浠栧湴鏂硅�屼笉鏄�鍒犻櫎瀹冧滑锛夈��

ALTER TABLE鐨勫伐浣滄柟寮忓�備笅锛�

• 鐢ㄨ�锋眰鐨勭粨鏋勫彉鍖栧垱寤哄悕涓�A-xxx鐨勬柊琛ㄣ��
• 灏嗘墍鏈夎�屼粠鍘熷�嬭〃鎷疯礉鍒�A-xxx銆�
• 灏嗗師濮嬭〃閲嶅懡鍚嶄负B-xxx銆�
• 灏�A-xxx閲嶅懡鍚嶄负鍘熷�嬭〃鐨勫悕绉般��
• 鍒犻櫎B-xxx銆�

濡傛灉鍦ㄩ噸鍛藉悕鎿嶄綔涓�鍑洪敊锛�MySQL灏嗗皾璇曟挙閿�鏇存敼銆傚�傛灉閿欒��寰堜弗閲嶏紙灏界�¤繖涓嶅簲鍑虹幇锛夛紝MySQL浼氬皢鏃ц〃淇濈暀涓�B-xxx銆傜畝鍗曞湴鍦ㄧ郴缁熺骇鍒�涓婇噸鍛藉悕琛ㄦ枃浠讹紝搴旇兘浣挎暟鎹�澶嶅師銆�

濡傛灉鍦ㄤ簨鍔℃�ц〃涓婁娇鐢�ALTER TABLE锛屾垨姝ｅ湪浣跨敤Windows鎴�OS/2鎿嶄綔绯荤粺锛屽�傛灉宸插湪琛ㄤ笂鎵ц�屼簡LOCK TABLE鎿嶄綔锛�ALTER TABLE灏嗗�硅〃鎵ц�岃В閿佹搷浣溿�傝繖鏄�鍥犱��InnoDB鍜岃繖绫绘搷浣滅郴缁熶笉鑳芥挙閿�姝ｅ湪浣跨敤鐨勮〃銆�

棣栧厛锛岃�疯�冭檻鏄�鍚︾殑纭�闇�瑕佹洿鏀硅〃涓�鐨勫垪椤哄簭銆�SQL鐨勬牳蹇冭�佺偣鏄�浠庢暟鎹�瀛樺偍鏍煎紡鑾峰彇搴旂敤銆傛�诲簲鎸囧畾妫�绱㈡暟鎹�鐨勯『搴忋�傚湪涓嬮潰鐨勭��1鏉¤��鍙ヤ腑锛屼互col_name1銆�col_name2銆�col_name3椤哄簭杩斿洖鍒楋紱鍦ㄧ��2鏉¤��鍙ヤ腑锛屼互col_name1銆�col_name3銆�col_name2椤哄簭杩斿洖鍒楋細

mysql> SELECT col_name1, col_name2, col_name3 FROM tbl_name;

mysql> SELECT col_name1, col_name3, col_name2 FROM tbl_name;

濡傛灉鍐冲畾鏇存敼琛ㄥ垪鐨勯『搴忥紝鍙�鎵ц�屼笅杩版搷浣滐細

1. 鐢ㄥ叿鏈夋柊椤哄簭鐨勫垪鍒涘缓鏂拌〃銆�
2. 鎵ц�岃�ヨ��鍙ワ細

mysql> INSERT INTO new_table

    -> SELECT columns-in-new-order FROM old_table;

3. 鎾ら攢鎴栭噸鍛藉悕old_table銆�
4. 灏嗘柊琛ㄩ噸鍛藉悕涓哄師濮嬪悕绉帮細

mysql> ALTER TABLE new_table RENAME old_table;

SELECT *鍗佸垎閫傚悎浜庢祴璇曟煡璇��備絾鏄�锛屽湪搴旂敤绋嬪簭涓�锛屾案杩滀笉瑕佷緷璧�SELECT *鐨勪娇鐢�锛屼笉瑕佷緷璧栨牴鎹�鍏朵綅缃�妫�绱㈠垪銆傚�傛灉娣诲姞銆佺Щ鍔ㄦ垨鍒犻櫎浜嗗垪锛屾墍杩斿洖鐨勫垪鐨勯『搴忓拰浣嶇疆涓嶄細淇濇寔鐩稿悓銆傚�硅〃缁撴瀯鐨勭畝鍗曟洿鏀逛篃浼氬�艰嚧搴旂敤绋嬪簭澶辫触銆�

涓嬮潰浠嬬粛浜嗗�逛娇鐢�TEMPORARY琛ㄧ殑闄愬埗锛�

• TEMPORARY琛ㄥ彧鑳芥槸HEAP銆�ISAM銆�MyISAM銆�MERGE銆佹垨InnoDB绫诲瀷銆�
• 鍦ㄧ浉鍚岀殑鏌ヨ��涓�锛屼笉鑳藉紩鐢�TEMPORARY琛�1娆′互涓娿�備緥濡傦紝涓嬩緥涓嶈兘姝ｅ父宸ヤ綔锛�

mysql> SELECT * FROM temp_table, temp_table AS t2;

閿欒��1137锛氫笉鑳藉啀娆℃墦寮�琛�锛�'temp_table'

• SHOW TABLES璇�鍙�涓嶄細鍒楀嚭TEMPORARY琛ㄣ��
• 涓嶈兘浣跨敤RENAME閲嶅懡鍚�TEMPORARY琛ㄣ��浣嗚兘浣跨敤ALTER TABLE鍙栬�屼唬涔嬶細

mysql> ALTER TABLE orig_name RENAME new_name;

涓嬮潰鍒楀嚭浜嗗凡鐭ラ棶棰橈紝鏇存�ｅ畠浠�鍏锋湁杈冮珮鐨勪紭鍏堢骇锛�

• 濡傛灉灏�NULL鍊间笌浣跨敤ALL/ANY/SOME鐨勫瓙鏌ヨ�㈣繘琛屾瘮杈冿紝鑰屼笖瀛愭煡璇㈣繑鍥�绌虹殑缁撴灉锛屾瘮杈冩搷浣滀細璇勪及NULL鐨勯潪鏍囧噯缁撴灉鑰屼笉鏄�TRUE鎴�FALSE銆傚湪MySQL 5.1涓�灏嗘洿姝ｈ�ラ棶棰樸��
• 瀵逛簬IN鐨勭嚎瀛愭煡璇�浼樺寲涓嶅儚鈥�=鈥濋偅鏍锋湁鏁堛��
• 鍗充娇浣跨敤浜�lower_case_table_names=2锛堝厑璁�MySQL璁颁綇鏁版嵁搴撳悕鍜岃〃鍚嶄娇鐢ㄧ殑澶у皬鍐欙級锛屽�逛簬鍑芥��DATABASE()鎴栧湪鍚勭�嶆棩蹇楀唴锛堝湪涓嶅尯鍒嗗ぇ灏忓啓鐨勭郴缁熶笂锛夛��MySQL涔熶笉浼氳�颁綇鏁版嵁搴撳悕浣跨敤鐨勫ぇ灏忓啓鎯呭喌銆�
• 鍦ㄥ�嶅埗鎿嶄綔涓�锛屾挙閿�FOREIGN KEY绾︽潫涓嶅伐浣滐紝杩欐槸鍥犱负绾︽潫鍙�鑳藉湪浠庢湇鍔″櫒涓婃湁鍙︿竴涓�鍚嶇О銆�
• REPLACE锛堜互鍙婂叿鏈�REPLACE閫夐」鐨�LOAD DATA锛変笉浼氳Е鍙�ON DELETE CASCADE銆�
• 濡傛灉鏈�浣跨敤鎵�鏈夊垪鑰屼笖浠呬娇鐢�DISTINCT鍒楄〃涓�鐨勫垪锛屽��GROUP_CONCAT()涓�锛�DISTINCT涓嶈兘涓�ORDER BY涓�璧峰伐浣溿��
• 濡傛灉1浣嶇敤鎴锋嫢鏈夐暱鏃堕棿杩愯�岀殑浜嬪姟锛岃�屼笖鍙�1浣嶇敤鎴锋挙閿�浜嗗湪浜嬪姟涓�鏇存柊鐨勬��1琛�锛岄偅涔堝湪琛ㄧ敤浜庝簨鍔℃湰韬�涔嬪墠锛屽瓨鍦ㄨ緝灏忕殑鏈轰細锛屼細鍦ㄤ簩杩涘埗鏃ュ織涓�鍖呭��DROP TABLE鍛戒护銆傛垜浠�璁″垝鏇存�ｈ�ラ棶棰橈紝鏂规硶鏄�璁�DROP TABLE鍛戒护绛夊緟锛岀洿鑷宠〃鏈�鍦ㄤ换浣曚簨鍔′腑浣跨敤涓烘瓈��
• 灏嗗ぇ鐨勬暣鏁板�硷紙浠嬩簬2⁶³鍜�2⁶⁴鈥�1涔嬮棿锛夋彃鍏ユ暟鍊兼垨瀛楃�︿覆鍒楁椂锛屽畠灏嗕綔涓鸿礋鍊兼彃鍏ワ紝杩欐槸鍥犱负璇ユ暟鍊兼槸鍦ㄦ湁绗﹀彿鏁存暟鐜�澧冧笅璇勪及鐨勩��
• 濡傛灉鏈嶅姟鍣ㄨ繍琛屽湪涓嶅叿澶囦簩杩涘埗鏃ュ織鍔熻兘鐨勬潯浠朵笅锛�FLUSH TABLES WITH READ LOCK涓嶈兘灞忚斀COMMIT锛屾墽琛屽畬鏁村�囦唤鏃惰繖鍙�鑳戒細瀵艰嚧闂�棰橈紙琛ㄩ棿鐨勪竴鑷存�ч棶棰橈級銆�
• 鍦ㄦ煇浜涙儏鍐典笅锛屼綔鐢ㄥ湪BDB琛ㄤ笂鐨�ANALYZE TABLE浼氬�艰嚧琛ㄤ笉鍙�鐢�锛岀洿鑷抽噸鍚�mysqld涓烘��銆傚�傛灉鍑虹幇璇ユ儏鍐碉紝璇峰��MySQL閿欒��鏂囦欢涓�鏌ユ壘涓嬭堪褰㈠紡鐨勯敊璇�锛�

001207 22:07:56  bdb:  log_flush: LSN past current end-of-log

• 鍦ㄦ墍鏈変簨鍔″畬鎴愪箣鍓嶏紝涓嶈�佸��BDB琛�锛堟�ｅ湪鍏朵笂杩愯�屽�氳��鍙ヤ簨鍔★級涓婃墽琛�ALTER TABLE锛堝彲鑳戒細蹇界暐浜嬪姟锛夈��
• 瀵逛簬姝ｅ湪浣跨敤INSERT DELAYED鐨勮〃锛屽湪鍏朵笂鎵ц��ANALYZE TABLE銆�OPTIMIZE TABLE鍜�REPAIR TABLE鏃讹紝鍙�鑳戒細瀵艰嚧闂�棰樸��
• 鍦ㄨ〃涓婃墽琛�LOCK TABLE ...鍜�FLUSH TABLES ...鏃讹紝涓嶄繚璇佹病鏈夊畬鎴愪竴鍗婄殑浜嬪姟銆�
• BDB琛�鎵撳紑鐨勯�熷害鐩稿�硅緝鎱�銆傚�傛灉浣犲湪鏁版嵁搴撲笂鏈夊緢澶�BDB琛�锛屽�傛灉鏈�浣跨��鈥�-A鈥�閫夐」鎴栨�ｄ娇鐢ㄥ啀娣风紪鍔熻兘锛岃�佹兂鍦ㄦ暟鎹�搴撲笂浣跨��mysql瀹㈡埛绔�锛岄渶瑕佽姳璐硅緝闀跨殑鏃堕棿銆傚綋浣犳湁澶х殑琛ㄩ珮閫熺紦鍐叉椂锛岃繖鐐瑰挨鍏舵槑鏄俱��
• 澶嶅埗鍔熻兘閲囩敤浜嗘煡璇㈢骇鏃ュ織鍔熻兘锛氫富鏈嶅姟鍣ㄥ皢宸叉墽琛岀殑鏌ヨ�㈠啓鍏ヤ簩杩涘埗鏃ュ織銆傝繖鏄�涓�绉嶉�熷害寰堝揩銆佺畝娲佸拰鏈夋晥鐨勮�板綍鏂规硶锛屽湪澶у�氭暟鎯呭喌涓嬪伐浣滆壇濂姐��

濡傛灉浠ョ壒瀹氱殑鏂瑰紡璁捐�℃煡璇�锛屼娇寰楁暟鎹�鏇存敼鏄�闈炲喅瀹氭�э紙閫氬父涓嶆帹鑽愶紝鍗充娇鍦ㄥ�嶅埗涔嬪�栦篃鍚屾牱锛夛紝涓绘湇鍔″櫒鍜屼粠鏈嶅姟鍣ㄤ笂鐨勬暟鎹�灏嗗彉寰椾笉鍚屻��

渚嬪�傦��

• 灏�0鎴朜ULL鍊兼彃鍏�AUTO_INCREMENT鍒椾腑鐨凜REATE ... SELECT鎴朓NSERT ... SELECT璇�鍙ャ��
• DELETE锛屽�傛灉浠庡叿鏈塐N DELETE CASCADE灞炴�х殑澶栭敭鐨勮〃涓�鍒犻櫎琛屻��
• REPLACE ... SELECT銆両NSERT IGNORE ... SELECT锛屽�傛灉鍦ㄦ彃鍏ョ殑鏁版嵁涓�鍏锋湁閲嶅�嶉敭銆�

褰撲笖浠呭綋鍓嶈堪鏌ヨ�㈡病鏈変繚璇佸喅瀹氳�岄『搴忕殑ORDER BY瀛愬彞鏃躲��

渚嬪�傦紝瀵逛簬涓嶅叿鏈�ORDER BY鐨�INSERT ... SELECT锛�SELECT鍙�鑳戒細浠ヤ笉鍚岀殑椤哄簭杩斿洖琛�锛堝畠浼氬�艰嚧鍏锋湁涓嶅悓绛夌骇鐨勮�岋紝浠庤�屽�艰��AUTO_INCREMENT鍒椾腑鐨勪笉鍚屾暟鍊硷級锛屽叿浣撴儏鍐靛彇鍐充簬浼樺寲鍣ㄥ湪涓绘湇鍔″櫒鍜屼粠鏈嶅姟鍣ㄤ笂鎵�浣滅殑閫夋嫨銆�

鍦ㄤ富鏈嶅姟鍣ㄥ拰浠庢湇鍔″櫒涓婏紝鏌ヨ�㈠皢杩涜�屼笉鍚岀殑浼樺寲锛屼粎褰擄細

• 浣跨敤涓嶅悓鐨勫瓨鍌ㄥ紩鎿庡湪涓绘湇鍔″櫒涓婅�屼笉鏄�浠庢湇鍔″櫒涓婁繚瀛樿〃銆傦紙鑳藉�熷湪涓绘湇鍔″櫒鍜屼粠鏈嶅姟鍣ㄤ笂浣跨敤涓嶅悓鐨勫瓨鍌ㄥ紩鎿庛�備緥濡傦紝濡傛灉浠庢湇鍔″櫒鍏锋湁杈冨皯鐨勫彲鐢ㄧ�佺洏绌洪棿锛屽彲浠ュ湪涓绘湇鍔″櫒涓婁娇鐢�InnoDB锛屼絾鍦� 浠庢湇鍔″櫒妗戜娇鐢�MyISAM锛夈��
• 鍦ㄤ富鏈嶅姟鍣ㄥ拰浠庢湇鍔″櫒涓婏紝MySQL缂撳啿鍖哄ぇ灏忔槸涓嶅悓鐨勶紙key_buffer_size绛夛級銆�
• 鍦ㄤ富鏈嶅姟鍣ㄥ拰浠庢湇鍔″櫒涓婅繍琛屼笉鍚岀殑MySQL鐗堟湰锛岀増鏈�闂寸殑浼樺寲鍣ㄤ唬鐮佷篃涓嶅悓銆�

璇ラ棶棰樹篃浼氬奖鍝嶄娇鐢�mysqlbinlog|mysql鐨勬暟鎹�搴撴仮澶嶃��

閬垮厤璇ラ棶棰樼殑鏈�绠�鍗曟柟娉曟槸锛屼负鍓嶈堪鐨勯潪鍐冲畾鎬ф煡璇㈠�炲��ORDER BY瀛愬彞锛屼互纭�淇濇�绘槸浠ョ浉鍚岀殑椤哄簭淇濆瓨鎴栨洿鏀硅�屻��

鍦ㄥ皢鏉ョ殑MySQL鐗堟湰涓�锛岄渶瑕佹椂锛屾垜浠�灏嗚嚜鍔ㄥ�炲姞ORDER BY瀛愬彞銆�

涓嬮潰鍒楀嚭浜嗗凡鐭ョ殑浜嬪疁锛岃繖浜涗簨瀹滃皢鍦ㄦ伆褰撶殑鏃跺�欐洿姝ｏ細

• 鏃ュ織鏂囦欢鍚嶅熀浜庢湇鍔″櫒涓绘満鍚嶏紙濡傛灉鏈�浣跨敤鍚�鍔ㄩ�夐」鎸囧畾鏂囦欢鍚嶇殑璇濓級銆傚�傛灉鏇存敼浜嗕富鏈哄悕锛屼綘灏嗕笉寰椾笉浣跨敤璇稿�傗��--log-bin=old_host_name-bin鈥濈瓑閫変笅缇庡浗銆傚彟涓�绉嶉�夋嫨鏄�閲嶅懡鍚嶆棫鏂囦欢锛屼互鍙嶆槧涓绘満鍚嶅彉鏇存儏鍐碉紙濡傛灉鏄�浜岃繘鍒舵棩蹇楋紝闇�瑕佺紪杈戜簩杩涘埗鏃ュ織绱㈠紩鏂囦欢锛屽苟鏇存��binlog鍚嶇О锛夈�傝�峰弬瑙�5.3.1鑺傦紝鈥�mysqld鍛戒护琛岄�夐」鈥�銆�
• Mysqlbinlog涓嶅垹闄ゆ墽琛�LOAD DATA INFILE鍛戒护鍚庨仐鐣欑殑涓存椂鏂囦欢銆傝�峰弬瑙�8.6鑺傦紝鈥渕ysqlbinlog锛氱敤浜庡�勭悊浜岃繘鍒舵棩蹇楁枃浠剁殑瀹炵敤宸ュ叿鈥�銆�
• RENAME涓嶈兘涓�TEMPORARY琛ㄤ竴璧峰伐浣滐紝涔熶笉鑳戒笌MERGE琛ㄤ腑浣跨敤鐨勮〃涓�璧峰伐浣溿��
• 鐢变簬琛ㄥ畾涔夋枃浠剁殑淇濆瓨鏂瑰紡锛屼笉鑳藉湪琛ㄥ悕銆佸垪鍚嶆垨鏋氫妇涓�浣跨敤瀛楃��255锛�CHAR(255)锛夈�傛寜鐓у畨鎺掞紝褰撴垜浠�瀹炴柦浜嗘柊鐨勮〃瀹氫箟鏍煎紡鏂囦欢鏃讹紝灏嗗��5.1鐗堜腑鏇存�ｈ�ラ棶棰樸��
• 浣跨敤SET CHARACTER SET鏃讹紝涓嶈兘鍦ㄦ暟鎹�搴撱�佽〃鍜屽垪鍚嶄腑浣跨敤杞�鎹㈢殑瀛楃�︺��
• 涓嶈兘鍦�LIKE ... ESCAPE涓�涓�ESCAPE涓�璧蜂娇鐢�鈥�_鈥欐垨鈥�%鈥欍��
• 濡傛灉浣犳湁1涓�DECIMAL鍒楋紝鍏朵腑锛岀浉鍚岀殑鏁板�间互涓嶅悓鐨勬牸寮忎繚瀛橈紙渚嬪�傦��+01.00銆�1.00銆�01.00锛夛紝GROUP BY鍙�鑳戒細灏嗘瘡涓�鍊煎綋浣滀笉鍚岀殑鍊笺��
• 浣跨敤MIT-pthreads鏃讹紝涓嶈兘鍦ㄥ彟涓�涓�鐩�褰曚笅鍒涘缓鏈嶅姟鍣ㄣ�傝繖鏄�鍥犱负瀹冮渶瑕佹洿鏀�MIT-pthreads锛屾垜浠�涓嶅お浼氭洿姝ｈ�ラ棶棰樸�傝�峰弬瑙�2.8.5 鈥�MIT-pthreads娉ㄦ剰浜嬮」鈥�銆�
• 鍦�GROUP BY銆�ORDER BY鎴�DISTINCT涓�锛屼笉鑳藉彲闈犲湴浣跨��BLOB鍜�TEXT鍊笺�傚湪杩欑被鎯呭喌涓嬶紝涓�BLOB鍊艰繘琛屾瘮杈冩椂锛屼粎浣跨敤鏈�鍓嶇殑max_sort_length瀛楄妭銆�max_sort_length鐨勯粯璁ゅ�兼槸1024锛屽彲鍦ㄦ湇鍔″櫒鍚�鍔ㄦ椂鎴栬繍琛屾椂鏇存敼瀹冦��
• 鏁板�艰�＄畻鏄�浣跨敤BIGINT鎴�DOUBLE锛堟�ｅ父鎯呭喌涓嬪潎涓�64浣嶉暱锛夎繘琛岀殑銆�浣犳墍鑳借幏寰楃殑绮惧害鍙栧喅浜庡嚱鏁般�傞�氱敤瑙勫垯鏄�浣嶅嚱鏁版槸鎸�BIGINT绮惧害鎵ц�岀殑锛�IF鍜�ELT()鏄�鎸�BIGINT鎴�DOUBLE绮惧害鎵ц�岀殑锛屽叾浣欑殑鍑芥暟鏄�鎸�DOUBLE绮惧害鎵ц�岀殑銆傚�逛簬闄や綅瀛楁�靛�栫殑鍏朵粬鏁帮紝濡傛灉澶т��63浣嶏紙9223372036854775807锛夛紝搴旈伩鍏嶄娇鐢�鏃犵�﹀彿闀�long鍊笺��
• 鍦�1涓�琛ㄤ腑锛屾渶澶氳兘鏈�255涓�ENUM鍜�SET鍒椼��
• 鍦�MIN()銆�MAX()浠ュ強鍏朵粬鑱氬悎鍑芥暟涓�锛�MySQL鐩�鍓嶄細鏍规嵁鍏跺瓧绗︿覆鍊兼瘮杈�ENUM鍜�SET鍒楋紝鑰屼笉鏄�鏍规嵁瀛楃�︿覆鍦ㄩ泦鍚堜腑鐨勭浉瀵逛綅缃�銆�
• mysqld_safe浼氬皢鏉ヨ嚜mysqld鐨勬墍鏈夋秷鎭�鍐嶅畾鍚戝��mysqld鏃ュ織銆備笌涔嬬浉鍏崇殑1涓�闂�棰樻槸锛屽�傛灉浣犳墽琛�mysqladmin refresh鍏抽棴骞跺啀娆℃墦寮�鏃ュ織锛�stdout鍜�stderr浠嶄細琚�閲嶅畾鍚戝埌鏃х殑鏃ュ織銆�濡傛灉浣犱互骞夸箟鏂瑰紡浣跨敤鈥�--log鈥�锛�搴旂紪杈�mysqld_safe浠ヨ�板綍鍒�host_name.err鑰屼笉鏄�host_name.log锛屼互渚块�氳繃鍒犻櫎瀹冨苟鎵ц��mysqladmin refresh锛屾柟渚垮湴鏀跺洖涓烘棫鏃ュ織鍒嗛厤鐨勭┖闂淬��
• 鍦�UPDATE璇�鍙ヤ腑锛屽垪浠庡乏鍚戝彸鏇存柊銆傚�傛灉寮曠敤浜嗗凡鏇存柊鐨勫垪锛屼綘灏嗗緱鍒版洿鏂板�艰�屼笉鏄�鍘熷�嬪�笺�備緥濡傦紝涓嬭堪璇�鍙ヤ細灏�KEY澧炲姞2锛岃�屼笉鏄�1锛�

mysql> UPDATE tbl_name SET KEY=KEY+1,KEY=KEY+1;

• 浣犲彲浠ュ湪鐩稿悓鏌ヨ��涓�寮曠敤澶氫釜涓存椂琛�锛屼絾涓嶈兘寮曠敤浠讳綍缁欏畾鐨勪复鏃惰〃1娆′互涓娿�備緥濡傦紝涓嬭堪璇�鍙ヤ笉鑳芥�ｅ父宸ヤ綔锛�

mysql> SELECT * FROM temp_table, temp_table AS t2;

閿欒��1137锛氫笉鑳藉啀娆℃墦寮�琛�锛�'temp_table'

• 褰撲綘鍦ㄨ仈鍚堟搷浣滀腑浣跨敤鈥滈殣鍚�鈥濆垪鏃讹紝涓庢湭浣跨敤闅愬惈鍒楃浉姣旓紝浼樺寲鍣ㄥ皢浠ヤ笉鍚岀殑鏂瑰紡澶勭悊DISTINCT銆傚湪鑱斿悎鎿嶄綔涓�锛岄殣鍚�鍒楀皢浣滀负缁撴灉鐨勭粍鎴愰儴浠借�℃暟锛堝嵆浣挎湭鏄剧ず锛夛紝浣嗗湪姝ｅ父鏌ヨ��涓�锛岄殣鍚�鍒椾笉鍙備��DISTINCT姣旇緝銆傚湪浠ュ悗锛屾垜浠�鍙�鑳戒細鏇存敼璇ユ儏鍐碉紝鍦ㄦ墽琛�DISTINCT鏃朵笉姣旇緝闅愬惈鍒椼��

渚嬪�傦��

SELECT DISTINCT mp3id FROM band_downloads

       WHERE userid = 9 ORDER BY id DESC;

浠ュ強

SELECT DISTINCT band_downloads.mp3id

       FROM band_downloads,band_mp3

       WHERE band_downloads.userid = 9

       AND band_mp3.id = band_downloads.mp3id

       ORDER BY band_downloads.id DESC;

鍦ㄧ��2绉嶆儏鍐典笅锛屼娇鐢�MySQL鏈嶅姟鍣�3.23.x锛屽彲鍦ㄧ粨鏋滈泦涓�鑾峰��2涓�绛夊悓琛岋紙杩欐槸鍥犱负锛岄殣钘�ID鍒椾腑鐨勫�煎彲鑳戒笉鍚岋級銆�

娉ㄦ剰锛屽湪缁撴灉闆嗕腑锛屼粎瀵逛笉鍚�ORDER BY鍒楃殑鏌ヨ�㈡墠浼氬嚭鐜拌�ユ儏鍐点��

• 濡傛灉鍦ㄨ繑鍥炵┖闆嗙殑鏌ヨ��涓婃墽琛�PROCEDURE锛屽湪鏌愪簺鎯呭喌涓嬶紝PROCEDURE涓嶈浆鎹㈠垪銆�
• 鍒涘缓鍏锋湁MERGE绫诲瀷鐨勮〃鏃讹紝涓嶆��鏌ュ熀鏈�琛ㄦ槸鍚﹀叿鏈夊吋瀹圭殑绫诲瀷銆�
• 濡傛灉浣跨敤ALTER TABLE涓�MERGE琛ㄤ腑浣跨敤鐨勮〃澧炲姞浜�UNIQUE绱㈠紩锛岀劧鍚庡湪MERGE琛ㄤ笂澧炲姞浜嗘�ｅ父绱㈠紩锛屽�傛灉鍦ㄨ〃涓�瀛樺湪鏃х殑銆侀潪UNIQUE閿�锛屽�逛簬杩欎簺琛�锛岄敭椤哄簭鏄�涓嶅悓鐨勩�傝繖鏄�鍥犱负锛�ALTER TABLE浼氬皢UNIQUE绱㈠紩鏀惧湪姝ｅ父绱㈠紩涔嬪墠锛屼互渚胯兘灏芥棭妫�娴嬪埌閲嶅�嶇殑閿�銆�