mysql常用

编辑

     
     

如果脚本往数据库中的某个字段插入中文

.encode('utf8')

 

alter table case_test_point modify column `tp_name` varchar(500) CHARACTER SET utf8mb4 NOT NULL;

    

alter table go_ppa_scaling_factor modify column `worst_corner` varchar(45) CHARACTER SET utf8mb4 DEFAULT NULL;

    

mysql 两表关联更新

update go_engage_ppa_product_item pi,go_engage_ppa_items p set pi.item_name=p.item_name where  p.id=pi.item_id ;

https://blog.51cto.com/u_16175458/6783960

    

mysql 自动更新时间

自动获取创建时间：timestamp not null default CURRENT_TIMESTAMP

自动获取更新时间：timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

Incorrect string value: '' for column '' at row 1 错误分析

 ALTER TABLE `email_notice`.`go_engage_ppa_process` MODIFY COLUMN `typical_corner` VARCHAR(45) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL;

 

  

mysql  datetime

MySQL导入数据库时报错：ERROR 1292 (22007): Incorrect datetime value: ‘0000-00-00 00:00:00’ for column ‘return_create_time’ at row 1

导入数据中时间字段的格式为 ‘0000-00-00 00:00:00’，报错ERROR 1292 (22007): Incorrect datetime value: ‘0000-00-00 00:00:00’ for column ‘return_create_time’ at row 1

原因中数据库的sql_mode包含 no_zero_date 模式，不支持‘0000-00-00 00:00:00’，可以使用以下方式进行修改

 show variables like 'sql_mode';
±--------------±----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
±--------------±----------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
±--------------±----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

临时解决方案：

mysql> set global sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

永久解决方案：

添加到my.cnf里边 mysql 5.7版本的配置文件为：/etc/mysql/mysql.conf.d/mysqld.cnf

sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

 

    
     
posted @ 2023-11-24 15:54  声声慢43  阅读(15)  评论(0)  编辑  收藏  举报