mysql You can’t specify target table for update in FROM clause解决方法

mysql You can’t specify target table for update in FROM clause解决方法
出现这个错误的原因是不能在同一个sql语句中,先select同一个表的某些值,然后再update这个表。

mysql> update message set content='Hello World' where id in(select min(id) from message group by uid);
ERROR 1093 (HY000): You can't specify target table 'message' for update in FROM clause

因为在同一个sql语句中,先select出message表中每个用户消息的最小id值,然后再更新message表,因此会出现 ERROR 1093 (HY000): You can’t specify target table ‘message’ for update in FROM clause 这个错误。

解决方法:select的结果再通过一个中间表select多一次,就可以避免这个错误

update message set content='Hello World' where id in( select min_id from ( select min(id) as min_id from message group by uid) as a );

mysql 连接闪断自动重连的方法(用在后台运行中的PHP代码)

mysql 连接闪断自动重连的方法(用在后台运行中的PHP代码)
当mysql断开连接 $_instance这个还是有值得 所以会报错 MySQL server has gone away 这个地方需要捕捉异常才可以或许到
需要 清空连接 $_instance 这样就可以重新连接 就会报错了

<?php
// 数据库操作类
class DB{

    // 保存数据库连接
    private static $_instance = null;

    // 连接数据库
    public static function get_conn($config){
        if(isset(self::$_instance) && !empty(self::$_instance)){
            return self::$_instance;
        }

        $dbhost = $config['host'];
        $dbname = $config['dbname'];
        $dbuser = $config['user'];
        $dbpasswd = $config['password'];
        $pconnect = $config['pconnect'];
        $charset = $config['charset'];

        $dsn = "mysql:host=$dbhost;dbname=$dbname;";
        try {
            $h_param = array(
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,   //设置错误级别
            );
            if ($charset != '') {
                $h_param[PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET NAMES ' . $charset; //设置默认编码
            }
         
            if ($pconnect) {
                $h_param[PDO::ATTR_PERSISTENT] = true;   //是否是长连接
            }
            $conn = new PDO($dsn, $dbuser, $dbpasswd, $h_param);

        } catch (PDOException $e) {
            throw new ErrorException('Unable to connect to db server. Error:' . $e->getMessage(), 31);
        }

        self::$_instance = $conn;
        return $conn;
    }

    // 执行查询
    public static function query($dbconn, $sqlstr, $condparam){
        $sth = $dbconn->prepare($sqlstr);
        try{
            $sth->execute($condparam);
        } catch (PDOException $e) {
            echo $e->getMessage().PHP_EOL;
            self::reset_connect($e->getMessage()); // 出错时调用重置连接
        }
        $result = $sth->fetchAll(PDO::FETCH_ASSOC);
        return $result;
    }

    // 重置连接
    public static function reset_connect($err_msg){
        if(strpos($err_msg, 'MySQL server has gone away')!==false){
            self::$_instance = null;
        }
    }

}
?>

PDO 查询mysql返回字段int变为String型解决方法

PDO 查询mysql返回字段int变为String型解决方法
使用PDO查询mysql数据库时,执行prepare,execute后,返回的字段数据全都变为字符型。

例如id在数据库中是Int的,查询后返回是String型。

对于PHP这种弱类型的语言,影响不大。在做API返回数据时,如果类型与数据库不一致,对于Java和Objective C这些强类型,影响就很大了。

<?php
$pdo = new PDO($dsn, $user, $pass, $param);

// 在创建连接后,加入
$pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
?>

这两句设置的作用
PDO::ATTR_STRINGIFY_FETCHES 提取的时候将数值转换为字符串。
PDO::ATTR_EMULATE_PREPARES 启用或禁用预处理语句的模拟。

mysql 查看当前使用的配置文件my.cnf的方法

mysql 查看当前使用的配置文件my.cnf的方法

ps aux|grep mysql|grep 'my.cnf'

如果上面的命令没有输出,表示没有设置使用指定目录的my.cnf。

如果没有设置使用指定目录的my.cnf,mysql启动时会读取安装目录根目录及默认目录下的my.cnf文件。
mysql –help|grep ‘my.cnf’
/etc/my.cnf, /etc/mysql/my.cnf, /usr/local/etc/my.cnf, ~/.my.cnf 这些就是mysql默认会搜寻my.cnf的目录,顺序排前的优先。

如果没有设置使用指定目录my.cnf文件及默认读取目录没有my.cnf文件,表示mysql启动时并没有加载配置文件,而是使用默认配置。

需要修改配置,可以在mysql默认读取的目录中,创建一个my.cnf文件(例如:/etc/my.cnf),把需要修改的配置内容写入,重启mysql后即可生效。

mysql函数concat与group_concat使用说明

mysql函数concat与group_concat使用说明
concat()函数

mysql> select concat(',',name,',') from `user`;
+--------------------------+
| concat(',',fdipzone,',') |
+--------------------------+
| ,fdipzone,               |
+--------------------------+
1 row in set (0.00 sec)

concat_ws() 函数

mysql> select concat_ws(',',country_code,phone,region) from `user`;
+------------------------------------------+
| concat_ws(',',country_code,phone,region) |
+------------------------------------------+
| 86,13794830550,GZ                        |
+------------------------------------------+
1 row in set (0.00 sec)

group_concat()函数

mysql> select * from `article_in_category`;
+----+------------+-------------+
| id | article_id | category_id |
+----+------------+-------------+
|  1 |          1 |           1 |
|  2 |          1 |           2 |
|  3 |          1 |           3 |
|  4 |          2 |           4 |
|  5 |          2 |           3 |
|  6 |          2 |           5 |
|  7 |          3 |           1 |
|  8 |          3 |           5 |
|  9 |          3 |           6 |
| 10 |          4 |           8 |
+----+------------+-------------+
mysql> select article_id,group_concat(category_id order by category_id asc) from `article_in_category` group by article_id;
+------------+----------------------------------------------------+
| article_id | group_concat(category_id order by category_id asc) |
+------------+----------------------------------------------------+
|          1 | 1,2,3                                              |
|          2 | 3,4,5                                              |
|          3 | 1,5,6                                              |
|          4 | 8                                                  |
+------------+----------------------------------------------------+
4 rows in set (0.00 sec)

ps:category_id order by category_id asc 这个是 字段里面的的排序 比方说1,2,3就是升序啦

注意:group_concat()函数对返回的结果有长度限制,默认为1024字节

mysql> set global group_concat_max_len=2048;
Query OK, 0 rows affected (0.03 sec)

mysql> show global variables like '%group_concat_max_len%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| group_concat_max_len | 2048  |
+----------------------+-------+

mysql导入大批量数据出现MySQL server has gone away的解决方法

mysql导入大批量数据出现MySQL server has gone away的解决方法


mysql> show global variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 4194304 |
+-

可以看到是4M,然后调大为256M(1024*1024*256)

mysql> set global max_allowed_packet=268435456;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'max_allowed_packet';
+--------------------+-----------+
| Variable_name      | Value     |
+--------------------+-----------+
| max_allowed_packet | 268435456 |
+--------------------+-----------+
1 row in set (0.00 sec)

注意:
使用set global命令修改 max_allowed_packet 的值,重启mysql后会失效,还原为默认值。

如果想重启后不还原,可以打开 my.cnf 文件,添加 max_allowed_packet = 256M 即可。

mysql大小写敏感配置

mysql大小写敏感配置
show global variables like ‘%lower_case%’;

show global variables like ‘%lower_case%’;

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | ON    |
| lower_case_table_names | 0     |
+------------------------+-------+

lower_case_file_system

表示当前系统文件是否大小写敏感,只读参数,无法修改。

ON 大小写不敏感
OFF 大小写敏感

lower_case_table_names

表示表名是否大小写敏感,可以修改。

lower_case_table_names = 0时,mysql会根据表名直接操作,大小写敏感。
lower_case_table_names = 1时,mysql会先把表名转为小写,再执行操作。

mysql 严格模式 Strict Mode

mysql 严格模式 Strict Mode

找到MySQL安装目录下的my.cnf(windows系统则是my.ini)文件

在sql_mode中加入STRICT_TRANS_TABLES则表示开启严格模式,如没有加入则表示非严格模式,修改后重启mysql即可

例如这就表示开启了严格模式:
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Strict Mode功能说明
不支持对not null字段插入null值
不支持对自增长字段插入”值
不支持text字段有默认值