the difference between 1,utf8_bin and Utf8_general_ci
CI is case insensitive, that is, "case-insensitive", A and a will be treated as the same in character judgments;
The bin is binary, and a and a will be treated differently.
For example you run:
SELECT * FROM table WHERE txt = ' a '
Then in Utf8_bin you can't find the line of txt = ' a ', and utf8_general_ci can.
2,myisam and InnoDB
InnoDB and MyISAM are the two most commonly used table types used by many people when using MySQL, each with its own pros and cons, depending on the specific application. The basic difference is that the MyISAM type does not support advanced processing, such as transaction processing, but InnoDB type support. Tables of the MyISAM type emphasize performance, which executes more than INNODB types faster, but does not provide transactional support, while InnoDB provides transactional support for advanced database features such as foreign keys.
The following are some details and specific implementations of the differences:
1. InnoDB does not support indexes of type Fulltext.
2. InnoDB does not save the exact number of rows in the table, that is, when the select count (*) from table is executed, InnoDB scans the entire table to calculate how many rows, but MyISAM simply reads out the saved rows. Note that when the COUNT (*) statement contains the Where condition, the operations of the two tables are the same.
3. For Auto_increment type fields, the InnoDB must contain only the index of the field, but in the MyISAM table, you can establish a federated index with the other fields.
4. When you delete the From table, InnoDB does not re-establish the table, but deletes one row at a time.
5. The LOAD table from master operation does not work for InnoDB, and the solution is to first change the InnoDB table to a MyISAM table, import the data and then change it to the InnoDB table, but not the table for the extra InnoDB attributes (such as foreign keys) used.
In addition, row locks on the InnoDB table are not absolute, and if MySQL cannot determine the range to scan when executing an SQL statement, the INNODB table will also lock the entire table, such as Update table set num=1 where name like "%aaa%"
The main difference between the two types is that InnoDB supports transaction processing with foreign keys and row-level locks. And MyISAM is not supported. So MyISAM are often considered only suitable for use in small projects.
I use MySQL as a user perspective, InnoDB and MyISAM are more like, but from my current operation of the database platform to achieve demand: 99.9% stability, convenient scalability and high availability, MyISAM is definitely my first choice.
The reasons are as follows:
1, first of all, I currently hosted on the platform most of the project is read more write less items, and MyISAM read performance is much stronger than the InnoDB.
2, MyISAM index and data are separate, and the index is compressed, memory usage rate on the corresponding increase a lot. Can load more indexes, while InnoDB is tightly bundled with the index and data, no compression is used to create innodb larger than MyISAM.
3, from the platform point of view, often 1, 2 months will occur application developers inadvertently update a table where the scope is not correct, resulting in the table can not be used normally, this time the superiority of the MyISAM is reflected, casually from the day copy of the compressed package to remove the corresponding table files, Casually put in a database directory, and then dump into SQL and then guide back to the main library, and the corresponding binlog to fill up. If it's InnoDB, I'm afraid it can't be that fast, don't talk to me. Let InnoDB periodically back up with the export xxx.sql mechanism, because the minimum amount of data for one of the smallest database instances on my platform is dozens of g in size.
4, from my contact with the application logic, select COUNT (*) and order BY is the most frequent, probably accounted for the entire SQL total statement more than 60% of the operation, and this operation InnoDB actually will lock the table, many people think InnoDB is row-level lock, That's just where the primary key is valid and the Non-key key will lock the entire table.
5, there is often a lot of application departments need me to give them regular data on certain tables, MyISAM words is very convenient, as long as they correspond to the table frm. myd,myi files, let themselves in the corresponding version of the database to start on the line, and InnoDB need to export the xxx.sql, because the light to others file, by the dictionary data file, the other side is not available.
6, if and myisam than insert write operation, InnoDB also can not reach the MyISAM write performance, if it is for indexing update operation, although MyISAM may be inferior innodb, but so high concurrent write, from the library can chase is also a problem, It is better to solve this problem by using a multi-instance sub-table architecture.
7, if it is using MyISAM, the merge engine can greatly speed up the development of the application department, they simply do some select count (*) operations on the merge table, very suitable for a large project volume of about hundreds of millions of of a certain type of rows (such as logs, survey statistics) business table.
Of course, InnoDB is not absolutely not, with business projects such as simulation of the stock project, I was using InnoDB, active users more than 200,000 times, is also very easy to cope with, so I personally also like InnoDB, but if from the database platform application, I would prefer MyISAM.
In addition, some people may say that you myisam can not resist too much write operation, but I can make up through the structure, say a database platform capacity of my existing use: Master and subordinate data total in hundreds of t above, more than 1 billion PV dynamic page every day, there are several big items is through the data interface way call not counted into PV total, ( This includes a large project because the initial memcached was not deployed, resulting in a single database processing 90 million of queries a day. My overall database server load averaged around 0.5-1.
What is the difference between 3,utf8_general_ci and utf8_unicode_ci?
The Utf8_unicode_ci proofing rules only partially support the Unicode collation algorithm, and some characters are still not supported.
UTF8_UNICODE_CI cannot fully support the combination of tokens.
Utf8_general_ci is a legacy proofing rule that does not support scaling, it can only be compared between characters. This means that the UTF8_GENERAL_CI proofing rules are relatively fast, but they are less accurate than the collation rules used by UTF8_UNICODE_CI.
The difference in application
1, for a language only when the use of utf8_unicode_ci sorting does not do well, the implementation of specific language-related UTF8 character set proofing rules. For example, UTF8_UNICODE_CI works well for German and French, so you no longer need to create special UTF8 proofing rules for both languages.
2, Utf8_general_ci also applies with German and French, except '? ' equals ' s ', not ' SS '. If your application can accept these, then you should use UTF8_GENERAL_CI because it is fast. Otherwise, use utf8_unicode_ci because it is more accurate.
With a word overview above this paragraph: utf8_unicode_ci more accurate, utf8_general_ci speed is relatively fast. Usually the accuracy of utf8_general_ci is enough for us to use, after I read a lot of program source, found that most of them are also used utf8_general_ci, so the general selection of new database utf8_general_ci can be
Reference:
Http://www.diybl.com/course/7_databases/mysql/myxl/20100721/474574.html
Chinese: http://hi.baidu.com/my_labs/blog/item/ea1a578360dc82ab0cf4d2c0.html
English: Http://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci
4, the table name contains special characters
Copy Code code as follows:
SELECT * from books where ' book-id ' = ' b001 ' Note is next to 1 on the numeric keypad ' not single quotes
SELECT * from ' book-cate '
It's best not to use special symbols with book_id.
