mysql 重复数据防止插入:)

时间：2023-03-08 17:37:37
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,1000000

replace 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);

		}

}

?>

　　

相关文章