mysql 重複資料防止插入:)

標籤：

insert into table (id, name, age) values(1, "A", 19) on duplicate key update name=values(name), age=values(age)

 /* 插入資料：如果有重複的則選擇更新; */

insert ignore into `testtable` (`mpass`,`pass`) select mpass,pass from rr_pass_0 limit 0,1000000replace into `testtable` (`mpass`,`pass`) select mpass,pass from rr_pass_0 limit 0,10

 //設定主鍵:如果有重複的資料選擇丟棄;

select *, count(distinct name) from table group by name

//查詢出重複的資料

這幾天寫了個匯入指令碼;留個筆記;

<?php error_reporting(0);ini_set(‘memory_limit‘, ‘1024M‘);header(‘Content-type: text/html;charset=UTF-8‘);$db_host = "127.0.0.1";$db_user = "root";$db_pass = "";$db_database = "qq";$table = "import_table"; //匯入的庫$tables = "query_tablle"; //查詢的庫//62300000,100000$dump_num = "100"; //按照一次10W的標準;$db = new db($db_host,$db_user,$db_pass,$db_database);//$test = $db->query("insert into testtables (a) values (‘145646464‘)");$start_num =5305950 ;//開始位置;$tables_query = $db->query("select count(*) from $tables");//查詢總數$tables_count_array = $db->sql_array($tables_query);$tables_count_num = $tables_count_array[0];$counts = intval($tables_count_num /  $dump_num);$counts = $counts + 1;$counts = 10000;for($i=0;$i<$counts;$i++){//echo ("select `pass` from rr1_pass limit $start_num,$dump_num ")."<br \>";$start_time = microtime(true);$sql_oking = $db->query("select `pass` from `$tables` limit $start_num,$dump_num ");while($sql_oking_array = $db->sql_array($sql_oking)){$pass = $sql_oking_array[‘pass‘];$check = $db->query("select pass from `$table` where pass = \"$pass\" limit 0,1 ");$check = $db->sql_nums($check);if(!empty($pass) && !$check ) {$md5 = md5($pass);$md5_2 = md5($pass);$db->query("insert into $table (md5,md5_2,pass) values (‘".$md5."‘,‘".$md5_2."‘,‘".$pass."‘) ") ;}}$end_time = microtime(true);$time = $end_time - $start_time ;echo "$start_num,$dump_num db_write runtime:".$time."s\r\n";$start_num = $start_num+$dump_num;}class db{private $db_host;private $db_user;private $db_pass;private $db_conn;private $db_database;private $sql;private $result;public function __construct($db_host,$db_user,$db_pass,$db_database,$db_conn = ‘‘ ){$this->db_host = $db_host;$this->db_user = $db_user;$this->db_pass = $db_pass;$this->db_database = $db_database;$this->db_conn = $db_conn;$this->connect();}public function connect(){$this->db_conn = @mysql_connect($this->db_host,$this->db_user,$this->db_pass);if(!$this->db_conn) {echo "mysql connect not okay!";}if(!mysql_select_db($this->db_database,$this->db_conn)){echo "select database not okay!";}}public function query($sql){$this->sql = $sql;$result = mysql_query($this->sql,$this->db_conn);$this->result = $result; #if(!$this->result){echo "sql query is not okay!";} exit;return $this->result;}public function sql_array($str = ‘‘){if($str !=‘‘){ return mysql_fetch_array($str);}else{ return mysql_fetch_array($this->result);}}public function sql_nums($sql){return mysql_num_rows($sql);}public function free(){@mysql_free_result($this->result);}public function __destruct(){if(!empty($this->result)){$this->free();}mysql_close($this->db_conn);}}?>

　　

mysql 重複資料防止插入:)