DATABASE

改 mysql 欄位寬

by

 ALTER TABLE `tbl_item` CHANGE `objno` `objno` VARCHAR( 20 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL 

[php] 讀 CSV 檔

by 
if ( empty($_SERVER["DOCUMENT_ROOT"]) ) $DOC_ROOT="/m1/root";
else $DOC_ROOT=$_SERVER["DOCUMENT_ROOT"];

include $DOC_ROOT."/db.inc.php";

$filename="test_data.dat";
$filename="d_0416.dat";
$max_aline=1000;

if ( !$__db_mysql=@mysql_connect($__config['host'],$__config['user'],$__config['pass'],FALSE, MYSQL_CLIENT_COMPRESS) ) {
  debug_log($process_log,"fail on connect db,0");
  die('fail on connect db,0');
}

mysql_select_db('demo',$__db_mysql);
mysql_query("set names big5", $__db_mysql);

setlocale(LC_ALL,'zh_TW.Big5');
$fp=fopen($filename,"r");

$cnt=0;
while ( $data=fgetcsv($fp,1000) ) {
  if ( count($data)!=48 ) continue;

  unset( $f_name );
  unset( $f_data );
  $f_data[]=0;
  foreach ($data as $k => $v) {
    $f_name[]="f_".$k;
    $f_data[]=sprintf("'%s'",substr($v,0,200));
  }
  $f_data[]=time();

  $sql=sprintf("insert into user2 values (%s)",implode(',',$f_data));
  printf("%d\n",$cnt);
  $result=mysql_query($sql, $__db_mysql);

  $cnt++;
}
fclose($fp);

mysql_close($__db_mysql);

// —–
function xdisp($data) {
  global $fp;
  $data=fgetcsv($fp,1000);
  printf("%d\n",count($data));
  print_r($data);
}

mysql c api 設定執行時的 timeout 時間

by 
int fnSetTimeout(int timeout) {
  char **row;
  char sql[100];
  char data[200];

  sprintf(sql,"set session wait_timeout=%d",timeout);
  if ( mysql_real_query( &mysql, sql, strlen(sql) ) ) {
    printf("FAILED : %s\n",sql);
    exit(3);
  }

  sprintf(sql,"set session interactive_timeout=%d",timeout);
  if ( mysql_real_query( &mysql, sql, strlen(sql) ) ) {
    printf("FAILED : %s\n",sql);
    exit(3);
  }

  strcpy(sql,"select @@session.interactive_timeout" );
  if ( mysql_real_query( &mysql, sql, strlen(sql) ) ) {
    printf("FAILED : %s\n",sql);
    exit(3);
  }

  if ( (result=mysql_store_result(&mysql)) != NULL ) {
    row=mysql_fetch_row(result);
    strcpy( data, row[0] );
    mysql_free_result( result );
    timeout=atoi(data);
  } else {
    timeout=0;
  }

  return(timeout);
}

mysql on syntax

by

 `c_timestamp` timestamp NOT NULL default ‘0000-00-00 00:00:00’ COMMENT ‘新增時間’,
 `m_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT ‘最後修改時間’,

useful mysql command

by

show status like ‘Qcache%’;

explain select …..

分析 schema 

echo “select * from tbl_item procedure analyse()\G” | mysql db_name > ana.txt