mysql中获得未有主键的表,脚本批量生成mysql测试数据

<?php
$Database_name = 'somedb';
$table = 'sometable';

     Define ('H', 'localhost'); define ('N', 'root') define ('P','password'); define ('T', $database_name);
     Mysql_connect (H, N, P) or die (mysql_error ()); mysql_select_db (T) or die (mysql_error ());

     function Escape($v){return mysql_real_escape_string($v);}

     Define('FILE', 'C:/Program Files/EasyPHP 2.0b1/www/'. $table .'/'. date ('d').'-'. date (' m').'-' . date ('Y').'.php');
     Define('FP', fopen(FILE,'w'));

     $aa = mysql_list_tables($database_name); while ($a = mysql_fetch_row($aa))
     {

             $bb = mysql_query ('SELECT * FROM'. $a[0].''); $tt = mysql_num_rows($bb); $nb = mysql_num_fields($bb);
             If($tt>0) 
             {
                 $p = 'INSERT INTO'. $a[0]. " VALUES ';

                 $t = 0;
                 while ($b = mysql_fetch_array($bb)) 
                 {
                     $ p .='(';
                     For ($i=0;$i<$nb, $i++) 

                     {
                            if ($i==0) 
                            {
                                $p .= "'$b[$i]'";
                            }
                            else
                            {
                                $p .=' , ''. Escape($b[$ i]). ''';
                            }
                     }
                     $t++;

                     if ($t<$tt) 
                     {
                         $ p .='),'."  n ";
                     }
                     else
                     {
                        $ p .=');'."  n";
                     }
                 } 
                 $p .= "nnn";
                 fwrite(FP, $p);
             }
     }

     fclose(FP);
?>

在做数据分析要找出来没有主键也没有唯一索引的表名,可以使用下面的sql

如下shell脚本通过while循环批量生成mysql的测试数据,你需要根据你自己的表结构来生成sql语句。

select distinct table_name from columns where  TABLE_SCHEMA = 'afs' and table_name not in (
select distinct table_name from COLUMNS 
where TABLE_SCHEMA = 'afs' and (column_key  = 'PRI' or column_key = 'UNI')
)
#!/bin/bash

i=1;
MAX_INSERT_ROW_COUNT=$1;
while [ $i -le $MAX_INSERT_ROW_COUNT ]
do
    mysql -uroot -proot afs -e "insert into afs_test (name,age,createTime) values ('HELLO$i',$i % 99,NOW());"
    d=$(date +%M-%d\ %H\:%m\:%S)
    echo "INSERT HELLO $i @@ $d"    
    i=$(($i+1))
    sleep 0.05
done

exit 0

发表评论

电子邮件地址不会被公开。 必填项已用*标注

标签:, , , , , ,
网站地图xml地图