PHP के साथ MySQL के लिए उपयोग करने के लिए सबसे अच्छा संयोजन क्या है?




encoding collation (9)

मैंने इन collation चार्ट उपयोगी पाया। http://collation-charts.org/mysql60/ । मुझे यकीन नहीं है कि उपयोग किया जाता है utf8_general_ci हालांकि।

उदाहरण के लिए utf8_swedish_ci के लिए चार्ट है। यह दिखाता है कि कौन से पात्र इसका अर्थ बताते हैं। http://collation-charts.org/mysql60/mysql604.utf8_swedish_ci.html

मैं सोच रहा हूं कि एक सामान्य वेबसाइट के लिए MySQL में collation के लिए "सर्वोत्तम" विकल्प है, जहां आप 100% सुनिश्चित नहीं हैं कि क्या दर्ज किया जाएगा? मैं समझता हूं कि सभी एन्कोडिंग समान होनी चाहिए, जैसे MySQL, Apache, HTML और PHP के अंदर कुछ भी।

अतीत में मैंने "यूटीएफ -8" में आउटपुट में PHP सेट किया है, लेकिन यह मिलान MySQL में कौन सा संयोजन करता है? मुझे लगता है कि यह यूटीएफ -8 में से एक है, लेकिन मैंने पहले utf8_unicode_ci , utf8_general_ci , और utf8_unicode_ci उपयोग किया है।


अपने डेटाबेस अपलोड फ़ाइल में, किसी भी पंक्ति से पहले फॉलोइन लाइन जोड़ें:

SET NAMES utf8;

और आपकी समस्या हल होनी चाहिए।


यूटीएफ -8 पाठ संबंधी जानकारी के लिए, आपको utf8_general_ci उपयोग करना चाहिए क्योंकि ...

  • utf8_bin : स्ट्रिंग में प्रत्येक वर्ण के बाइनरी मान द्वारा तारों की तुलना करें

  • utf8_general_ci : सामान्य भाषा नियमों का उपयोग करके स्ट्रिंग की तुलना करें और केस-असंवेदनशील तुलना का उपयोग करें

उर्फ इसे डेटा को तेजी से / अधिक कुशल / अधिक उपयोगी बनाने और अनुक्रमणित करना होगा।


संयोजनों को प्रभावित करता है कि डेटा को कैसे क्रमबद्ध किया जाता है और तारों की तुलना एक दूसरे से कैसे की जाती है। इसका मतलब है कि आपको उस संयोजन का उपयोग करना चाहिए जो आपके अधिकांश उपयोगकर्ता अपेक्षा करते हैं।

documentation से उदाहरण:

utf8_general_ci जर्मन और फ्रेंच दोनों के लिए भी संतोषजनक है, सिवाय इसके कि 'ß' बराबर है, और 'एसएस' के बराबर नहीं है। यदि यह आपके आवेदन के लिए स्वीकार्य है, तो आपको utf8_general_ci उपयोग करना चाहिए क्योंकि यह तेज़ है। अन्यथा, utf8_unicode_ci उपयोग करें क्योंकि यह अधिक सटीक है।

तो - यह आपके अपेक्षित उपयोगकर्ता आधार पर निर्भर करता है और आपको सही सॉर्टिंग की कितनी आवश्यकता है । एक अंग्रेजी उपयोगकर्ता आधार के लिए, utf8_general_ci पर्याप्त होना चाहिए, स्वीडिश जैसी अन्य भाषाओं के लिए, विशेष कॉलेशन बनाए गए हैं।


अनिवार्य रूप से, यह इस बात पर निर्भर करता है कि आप एक स्ट्रिंग के बारे में क्या सोचते हैं।

गुस द्वारा हाइलाइट की गई समस्या के कारण मैं हमेशा utf8_bin का उपयोग करता हूं। मेरी राय में, जहां तक ​​डेटाबेस का संबंध होना चाहिए, एक स्ट्रिंग अभी भी एक स्ट्रिंग है। एक स्ट्रिंग कई यूटीएफ -8 वर्ण है। एक चरित्र में द्विआधारी प्रतिनिधित्व होता है तो आपको उस भाषा को जानने की आवश्यकता क्यों होती है जिसका आप उपयोग कर रहे हैं? आम तौर पर, लोग बहुभाषी साइटों के दायरे वाले सिस्टम के लिए डेटाबेस का निर्माण करेंगे। यह एक सेट सेट के रूप में यूटीएफ -8 का उपयोग करने का पूरा बिंदु है। मैं एक शुद्धवादी हूं लेकिन मुझे लगता है कि बग जोखिम सूचकांक पर मिलने वाले मामूली लाभ से काफी अधिक है। किसी भी भाषा से संबंधित नियम डीबीएमएस की तुलना में बहुत अधिक स्तर पर किए जाने चाहिए।

मेरी किताबों में "मूल्य" में कभी भी दस लाख वर्षों में "valúe" के बराबर नहीं होना चाहिए।

यदि मैं एक टेक्स्ट फ़ील्ड को स्टोर करना चाहता हूं और केस असंवेदनशील खोज करना चाहता हूं, तो मैं PHP कार्यों जैसे LOWER () और php function strtolower () के साथ MYSQL स्ट्रिंग फ़ंक्शंस का उपयोग करूंगा।


असल में, आप शायद utf8_unicode_ci या utf8_general_ci का उपयोग करना चाहते हैं।

  • utf8_general_ci सभी उच्चारणों को छीनकर और सॉर्ट करना जैसे कि यह ASCII था
  • utf8_unicode_ci यूनिकोड सॉर्ट ऑर्डर का उपयोग करता है, इसलिए यह अधिक भाषाओं में सही तरीके से utf8_unicode_ci करता है

हालांकि, अगर आप केवल अंग्रेजी पाठ को स्टोर करने के लिए इसका उपयोग कर रहे हैं, तो इन्हें अलग नहीं होना चाहिए।


मुख्य अंतर सटीकता को सॉर्ट करना है (भाषा में वर्णों की तुलना करते समय) और प्रदर्शन। केवल एक विशेष utf8_bin है जो बाइनरी प्रारूप में वर्णों की तुलना करने के लिए है।

utf8_general_ci utf8_unicode_ci से कुछ हद तक तेज है, लेकिन कम सटीक (सॉर्टिंग के लिए)। विशिष्ट भाषा utf8 एन्कोडिंग (जैसे utf8_swedish_ci ) में अतिरिक्त भाषा नियम होते हैं जो उन्हें उन भाषाओं के क्रमबद्ध करने के लिए सबसे सटीक बनाते हैं। अधिकांश समय मैं utf8_unicode_ci (मैं छोटे प्रदर्शन सुधारों के लिए सटीकता पसंद करता हूं) का उपयोग करता हूं, जब तक कि मेरे पास एक विशिष्ट भाषा को प्राथमिकता देने का कोई अच्छा कारण न हो।

आप MySQL मैन्युअल पर विशिष्ट यूनिकोड वर्ण सेट पर और अधिक पढ़ सकते हैं - http://dev.mysql.com/doc/refman/5.0/en/charset-unicode-sets.html


कॉलेशन utf8mb4_unicode_ci साथ वर्ण सेट utf8mb4 का उपयोग करना सबसे अच्छा है।

चरित्र सेट, utf8 , केवल यूटीएफ -8 कोड बिंदुओं की एक छोटी राशि का समर्थन करता है, संभावित पात्रों में से लगभग 6%। utf8 केवल मूल बहुभाषी विमान (बीएमपी) का समर्थन करता है। वहां 16 अन्य विमान हैं। प्रत्येक विमान में 65,536 वर्ण होते हैं। utf8mb4 सभी 17 विमानों का समर्थन करता है।

MySQL 4 बाइट यूटीएफ -8 अक्षरों को दूषित कर देगा जिसके परिणामस्वरूप दूषित डेटा होगा।

utf8mb4 वर्ण सेट को 2010-03-24 को MySQL 5.5.3 में पेश किया गया था।

नए चरित्र सेट का उपयोग करने के लिए आवश्यक कुछ बदलाव छोटे नहीं हैं:

  • परिवर्तन आपके एप्लिकेशन डेटाबेस एडाप्टर में किए जाने की आवश्यकता हो सकती है।
  • बदलावों को my.cnf में करने की आवश्यकता होगी, जिसमें चरित्र सेट, संयोजन और barracuda में innodb_file_format को स्विच करना शामिल है
  • एसक्यूएल निर्माण कथन में शामिल करने की आवश्यकता हो सकती है: ROW_FORMAT=DYNAMIC
    • VARCHAR (1 9 2) और बड़ी पर इंडेक्स के लिए डायनामिक की आवश्यकता है।

नोट: Antelope से Antelope स्विच करने के लिए, एक बार से अधिक MySQL सेवा को पुनरारंभ करने की आवश्यकता हो सकती है। innodb_file_format_max तब तक नहीं बदलता जब तक MySQL सेवा को पुनरारंभ नहीं किया जाता है: innodb_file_format = barracuda

MySQL पुराने Antelope इनो डीबी फ़ाइल प्रारूप का उपयोग करता है। Barracuda गतिशील पंक्ति स्वरूपों का समर्थन करता है, यदि आप charset पर स्विच करने के बाद अनुक्रमणिका और कुंजी बनाने के लिए SQL त्रुटियों को हिट नहीं करना चाहते हैं, तो आपको आवश्यकता होगी: utf8mb4

  • # 170 9 - इंडेक्स कॉलम आकार बहुत बड़ा है। अधिकतम कॉलम आकार 767 बाइट्स है।
  • # 1071 - निर्दिष्ट कुंजी बहुत लंबी थी; अधिकतम कुंजी लंबाई 767 बाइट है

निम्नलिखित परिदृश्य का परीक्षण MySQL 5.6.17 पर किया गया है: डिफ़ॉल्ट रूप से, MySQL इस तरह कॉन्फ़िगर किया गया है:

SHOW VARIABLES;

innodb_large_prefix = OFF
innodb_file_format = Antelope

अपनी MySQL सेवा को रोकें और अपने मौजूदा my.cnf में विकल्प जोड़ें:

[client]
default-character-set= utf8mb4

[mysqld]
explicit_defaults_for_timestamp = true
innodb_large_prefix = true
innodb_file_format = barracuda
innodb_file_format_max = barracuda
innodb_file_per_table = true

# Character collation
character_set_server=utf8mb4
collation_server=utf8mb4_unicode_ci

उदाहरण एसक्यूएल कथन बनाएं:

CREATE TABLE Contacts (
 id INT AUTO_INCREMENT NOT NULL,
 ownerId INT DEFAULT NULL,
 created timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 contact VARCHAR(640) NOT NULL,
 prefix VARCHAR(128) NOT NULL,
 first VARCHAR(128) NOT NULL,
 middle VARCHAR(128) NOT NULL,
 last VARCHAR(128) NOT NULL,
 suffix VARCHAR(128) NOT NULL,
 notes MEDIUMTEXT NOT NULL,
 INDEX IDX_CA367725E05EFD25 (ownerId),
 INDEX created (created),
 INDEX modified_idx (modified),
 INDEX contact_idx (contact),
 PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB ROW_FORMAT=DYNAMIC;
  • यदि आप CREATE कथन से ROW_FORMAT=DYNAMIC हटा दिए गए हैं, तो आप INDEX contact_idx (contact) लिए उत्पन्न त्रुटि # 170 9 देख सकते हैं।

नोट: contact पर पहले 128 वर्णों तक सीमित करने के लिए इंडेक्स को बदलना ROW_FORMAT=DYNAMIC का उपयोग ROW_FORMAT=DYNAMIC साथ करने के लिए आवश्यकता को समाप्त करता है

INDEX contact_idx (contact(128)),

यह भी ध्यान दें: जब यह कहता है कि क्षेत्र का आकार VARCHAR(128) , तो यह 128 बाइट्स नहीं है। आप 128, 4 बाइट वर्ण या 128, 1 बाइट वर्णों का उपयोग कर सकते हैं।

इस INSERT कथन में 2 पंक्ति में 4 बाइट 'पू' वर्ण होना चाहिए:

INSERT INTO `Contacts` (`id`, `ownerId`, `created`, `modified`, `contact`, `prefix`, `first`, `middle`, `last`, `suffix`, `notes`) VALUES
(1, NULL, '0000-00-00 00:00:00', '2014-08-25 03:00:36', '1234567890', '12345678901234567890', '1234567890123456789012345678901234567890', '1234567890123456789012345678901234567890', '12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678', '', ''),
(2, NULL, '0000-00-00 00:00:00', '2014-08-25 03:05:57', 'poo', '12345678901234567890', '💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩', '💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩', '💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩', '', ''),
(3, NULL, '0000-00-00 00:00:00', '2014-08-25 03:05:57', 'poo', '12345678901234567890', '💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩', '💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩', '123💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩', '', '');

आप last कॉलम द्वारा उपयोग की जाने वाली जगह की मात्रा देख सकते हैं:

mysql> SELECT BIT_LENGTH(`last`), CHAR_LENGTH(`last`) FROM `Contacts`;
+--------------------+---------------------+
| BIT_LENGTH(`last`) | CHAR_LENGTH(`last`) |
+--------------------+---------------------+
|               1024 |                 128 | -- All characters are ASCII
|               4096 |                 128 | -- All characters are 4 bytes
|               4024 |                 128 | -- 3 characters are ASCII, 125 are 4 bytes
+--------------------+---------------------+

अपने डेटाबेस एडाप्टर में, आप अपने कनेक्शन के लिए वर्णमाला और संयोजन सेट करना चाह सकते हैं:

SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'

PHP में, यह सेट किया जाएगा: \PDO::MYSQL_ATTR_INIT_COMMAND

संदर्भ:


इस समस्या के बारे में बहुत जागरूक रहें जो utf8_general_ci का उपयोग करते समय हो सकता है।

यदि utf8_general_ci संयोजन का उपयोग किया जाता है, तो MySQL चुनिंदा वक्तव्यों में कुछ वर्णों के बीच अंतर नहीं करेगा। यह बहुत ही बदसूरत बग का कारण बन सकता है - खासकर उदाहरण के लिए, जहां उपयोगकर्ता नाम शामिल हैं। डेटाबेस टेबल का उपयोग करने वाले कार्यान्वयन के आधार पर, यह समस्या दुर्भावनापूर्ण उपयोगकर्ताओं को व्यवस्थापक खाते से मेल खाने वाले उपयोगकर्ता नाम बनाने की अनुमति दे सकती है।

यह समस्या स्वयं को 5.x संस्करणों में कम से कम प्रकट करती है - मुझे यकीन नहीं है कि यह व्यवहार बाद में बदल गया है या नहीं।

मैं कोई डीबीए नहीं हूं, लेकिन इस समस्या से बचने के लिए, मैं हमेशा केस-असंवेदनशील के बजाय utf8-bin साथ जाता हूं।

नीचे दी गई स्क्रिप्ट उदाहरण के द्वारा समस्या का वर्णन करती है।

-- first, create a sandbox to play in
CREATE DATABASE `sandbox`;
use `sandbox`;

-- next, make sure that your client connection is of the same 
-- character/collate type as the one we're going to test next:
charset utf8 collate utf8_general_ci

-- now, create the table and fill it with values
CREATE TABLE `test` (`key` VARCHAR(16), `value` VARCHAR(16) )
    CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO `test` VALUES ('Key ONE', 'value'), ('Key TWO', 'valúe');

-- (verify)
SELECT * FROM `test`;

-- now, expose the problem/bug:
SELECT * FROM test WHERE `value` = 'value';

--
-- Note that we get BOTH keys here! MySQLs UTF8 collates that are 
-- case insensitive (ending with _ci) do not distinguish between 
-- both values!
--
-- collate 'utf8_bin' doesn't have this problem, as I'll show next:
--

-- first, reset the client connection charset/collate type
charset utf8 collate utf8_bin

-- next, convert the values that we've previously inserted in the table
ALTER TABLE `test` CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;

-- now, re-check for the bug
SELECT * FROM test WHERE `value` = 'value';

--
-- Note that we get just one key now, as you'd expect.
--
-- This problem appears to be specific to utf8. Next, I'll try to 
-- do the same with the 'latin1' charset:
--

-- first, reset the client connection charset/collate type
charset latin1 collate latin1_general_ci

-- next, convert the values that we've previously inserted
-- in the table
ALTER TABLE `test` CONVERT TO CHARACTER SET latin1 COLLATE latin1_general_ci;

-- now, re-check for the bug
SELECT * FROM test WHERE `value` = 'value';

--
-- Again, only one key is returned (expected). This shows 
-- that the problem with utf8/utf8_generic_ci isn't present 
-- in latin1/latin1_general_ci
--
-- To complete the example, I'll check with the binary collate
-- of latin1 as well:

-- first, reset the client connection charset/collate type
charset latin1 collate latin1_bin

-- next, convert the values that we've previously inserted in the table
ALTER TABLE `test` CONVERT TO CHARACTER SET latin1 COLLATE latin1_bin;

-- now, re-check for the bug
SELECT * FROM test WHERE `value` = 'value';

--
-- Again, only one key is returned (expected).
--
-- Finally, I'll re-introduce the problem in the exact same 
-- way (for any sceptics out there):

-- first, reset the client connection charset/collate type
charset utf8 collate utf8_generic_ci

-- next, convert the values that we've previously inserted in the table
ALTER TABLE `test` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- now, re-check for the problem/bug
SELECT * FROM test WHERE `value` = 'value';

--
-- Two keys.
--

DROP DATABASE sandbox;




collation