mysql 笔记

//mysql 追加和替换函数

显示表的状态:SHOW TABLE STATUS LIKE '{$table1}'

追加：$sql="UPDATE `wap_pic_tag_name` SET `tag_pic` =  CONCAT(`tag_pic`,'$nextid,') WHERE id={$arow['id']}";

替换：$sql='UPDATE `wap_pic_tag_name` SET `tag_pic` = replace(tag_pic,"'.$id.',","") WHERE `tag_pic` like "%,'.$id.',%"';

 得到最后一条纪录id：

 SELECT LAST_INSERT_ID();

mysql>set wait_timeout = 10; 对当前交互链接有效；
mysql>set interactive_timeout = 10; 对后续起的交互链接有效；

//如果记录不存在写入，存在修改
$sql = "INSERT INTO `admin_workload` (`adminid`,`date`,`".$type_str."`) VALUES ('$adminid',DATE(NOW()),'1') ON DUPLICATE KEY UPDATE `".$type_str."`=`".$type_str."`+1";

 //unix 一周之前 时间戳
 from_unixtime(unix_timestamp()-7*24*3600) //字段类型 datetime


//字段名为变量的查询方法
$link = mysqli_connect('127.0.0.1', 'root', 'root', 'zsc');
$result =mysqli_multi_query($link, "SELECT `column` INTO @col1 FROM `t1` WHERE id =1; SELECT `column` INTO @col2 FROM `t1` WHERE id =2; SET @sqlstr = CONCAT( \"select \", @col1 , \",\", @col2 , \" from t2\" ) ; PREPARE stmt1 FROM @sqlstr ; EXECUTE stmt1;");
do {
    if ($result = mysqli_store_result($link)) {
        while ($row = mysqli_fetch_assoc($result)) {
            echo '<pre>';
            var_dump($row);
            echo '</pre>';
        }
        mysqli_free_result($result);
    }
   if (mysqli_more_results($link)) {
        printf("-----------------<br/>");
    }
} while (mysqli_next_result($link));