<?php mysql_query(“SET NAMES ‘utf8′”); ?>
由於php mysql set utf8_general_ci, 但是在table column set左latin1_swedish_ci, 就會產生
#1267 – Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation ‘like’
SELECT * FROM `table` WHERE `field` like ‘%這些%’
改成
SELECT * FROM `table` WHERE `field` like ‘%這些%’ COLLATE utf8_general_ci
CREATE TABLE `test` (`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE = MYISAM
今次 測試insert情況,
INSERT INTO `test2` VALUES (0)
在測試所見, 如果id 是零, 它會在最後insert
第2次INSERT 我嘗試 -1
INSERT INTO `test2` VALUES (-1)
結果是順利INSERT -1
第3次INSERT我再嘗試零
INSERT INTO `test2` VALUES (0)
結果也一樣, 在最後出現
(雖然這個測試好似好淺, 但這個是非常重要..)
Important: 在傳送資料(在一個舊table資料傳送一個新table), 如果有id 是零是非常嚴重
例如:
CREATE TABLE `test`.`test2` (`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`data` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL) ENGINE = MYISAM
CREATE TABLE `test`.`test3` (`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`data` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL) ENGINE = MYISAM
舊table
| id | data |
| 0 | abc |
| 1 | def |
| 2 | ghi |
INSERT IGNORE INTO test3
SELECT *
FROM test2
當資料傳送後, 新table的情況,
| id | data |
| 1 | abc |
| 2 | ghi |
在測試時, 其實insert不一定是跟id 去insert
有可能insert的次序是有機會亂(也可能是 1, 0,2)
如果id是 fk 那資料就全錯了
CREATE TABLE `test`.`test2` (
) ENGINE = MYISAM
這是一個非常有興趣測試，我嘗試設置一個field是id int(1), 另一個filed是name varchar(255)
CREATE TABLE `test` (
) ENGINE = innodb;
當我進行9次insert name的時候, 我就疑問, 第十個insert 將會發生error,
可是我繼續insert的時候, 他是沒有發生這個錯誤, 而且可以繼續增加到int 直到最大數目 – 2147483647
insert into `test` (`name`) VALUES (‘name 1’), (‘name 2’), (‘name 3’), (‘name 4’), (‘name 5’), (‘name 6’), (‘name 7’), (‘name 8’), (‘name 9’), (‘name 10’)
測試後其實int 是可以超出設定上限, 直到最大上限
覺得有趣的是設定上限後, 也能超出, 為什麼mysql不一開始就好似text, 不用預設一些上限
總是我不設int上限, 他也會幫我設定int(11)
Assuming a nicely formatted ‘|’ delimited text file that we want to insert into the table above, take this piece of PHP code:
if (!($fp = fopen("datafile.txt","r"))) { // open the file for reading
print "\nUnable to open datafile.txt for writing"; // display error
exit(); // end the running of the program
}
while (!feof ($fp)) { // loop through the file line by line
$sline = fgets($fp, 4096); // put the data into the variable $sline
$sline = chop($sline); // remove the newline
list($eno,$fname,$sname,$telno,$salary) = split("|",$code);
// split the line on "|", populating the ind. variables
$db->query("insert into employee(employee_number,firstname,surname,
tel_no, salary
values($eno,'$fname','$sname','$tel_no', $salary)");
} // end while loop
This would work, but would be very slow. The index buffer would be flushed after every insert. Until recently, MyISAM tables (The MySQL default) did not allow data to be inserted at the same time as being read. The new format does, but only if there are no deleted records present (highly unlikely in a heavily used system). So the entire table is locked for the duration of each insert. Fine on a low volume site, but when you’re getting hundreds or thousands of queries per second, you’ll soon notice the backlog!
There’s a solution however – the best way to insert the data is to use MySQL’s “LOAD DATA INFILE“. This is much faster (20 times according to MySQL), and the only way some systems I’ve seen are still hanging in there!
The syntax is simple, and the code becomes a lot simpler too:
$db->query("LOAD DATA INFILE 'datafile.txt' INTO TABLE employee (employee_number,firstname,surname,tel_no,salary) FIELDS TERMINATED BY '|'");
LOAD DATA INFILE has defaults of:
FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
if you don’t specify any of these clauses. And, just as with an ordinary insert, you need to specify a field list if the order of the fields is different, or, as in the example above, you’re not inserting data for every field. Always specifying a field list is good practice for all queries anyway – if someone adds a field to the table at a later stage, you don’t want to go back and have to fix all your previous INSERT and SELECT * statements.
If you can’t get this to work properly, have a look at the format of your text file – every problem I’ve seen with LOAD DATA has been because of a corrupted text file. Every field in every row must be delimited correctly!
You may not always be inserting from a text file – perhaps your application needs to do many unrelated inserts continually. There are ways to make sure the mass of users selecting are not badly affected… The first is to use INSERT LOW PRIORITY. This waits until there are no more reads waiting to happen, waiting for the gap, and not pushing in as it were. Of course, if your database is a rush hour special, there may never be a gap, and the client performing the INSERT LOW PRIORITY may start to grow cobwebs! An alternative here is INSERT DELAYED. The client is immediately freed, and the insert put into a queue (with all the other INSERT DELAYED‘s still waiting for the queue to end). This means that there can be no meaningful information passed back to the client, (such as the auto_increment value), as the INSERT has not been processed when the client is freed. Also, be aware that a catastrophe such as an unexpected power failure here will result in the queued INSERT‘s being lost. For neither of these methods do you have any idea when the data will be inserted, if at all, so I suggest you use with caution.