收录日常用到的一些经典语句。
5.7
MySQL客户端看到乱码
# 如果看到汉字为乱码
mysql> select f_id,f_catalog,f_name from t_abcjs_staff;
+--------+-----------+--------------------------+
| f_id | f_catalog | f_name |
+--------+-----------+--------------------------+
| 1 | 415 | Allegro in F |
| 2 | 416 | Always with Me |
| 3 | 417 | The Muppet Show Theme |
| 4 | 418 | The Quiet of the Night |
| 5 | 419 | ???? |
| 6 | 420 | ?????? |
| 7 | 421 | Expectation Waltz |
| 8 | 422 | Mosquitto |
| 9 | 423 | Blue Danube |
| 10 | 424 | Merry Christmas |
| 11 | 425 | ???? |
# 查看当前的变量
mysql> SHOW VARIABLES LIKE 'character_set_%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
# 把上面的client,connection,results都设置为utf8mb4即可
mysql> set character_set_client="utf8mb4";
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_connection="utf8mb4";
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_connection="utf8mb4";
Query OK, 0 rows affected (0.00 sec)
# 再次查看
mysql> SHOW VARIABLES LIKE 'character_set_%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
# 此时可以看到汉字
mysql> select f_id,f_name,f_catalog from t_abcjs_staff limit 10;
+------+------------------------+-----------+
| f_id | f_name | f_catalog |
+------+------------------------+-----------+
| 1 | Allegro in F | 415 |
| 2 | Always with Me | 416 |
| 3 | The Muppet Show Theme | 417 |
| 4 | The Quiet of the Night | 418 |
| 5 | 新年快乐 | 419 |
| 6 | 西班牙斗牛曲 | 420 |
| 7 | Expectation Waltz | 421 |
| 8 | Mosquitto | 422 |
| 9 | Blue Danube | 423 |
| 10 | Merry Christmas | 424 |
+------+------------------------+-----------+
10 rows in set (0.00 sec)
获得表记录排序序号
SET @rn = 0;
update t_abcjs_staff tas join
(SELECT
f_id,
@rn := @rn + 1 AS new_sort_num -- 每次查询后序号 +1
FROM t_abcjs_staff
ORDER BY f_id )t2 on tas.f_id = t2.f_id
set tas.f_catalog=t2.new_sort_num实际操作记录如下：
# 查看当前记录，
mysql> select f_id,f_catalog,f_name from t_abcjs_staff;
+--------+-----------+--------------------------+
| f_id | f_catalog | f_name |
+--------+-----------+--------------------------+
| 1 | 0 | Allegro in F |
| 2 | 0 | Always with Me |
| 3 | 0 | The Muppet Show Theme |
| 4 | 0 | The Quiet of the Night |
| 5 | 0 | 新年快乐 |
| 6 | 0 | 西班牙斗牛曲 |
| 7 | 0 | Expectation Waltz |
| 8 | 0 | Mosquitto |
| 9 | 0 | Blue Danube |
| 10 | 0 | Merry Christmas |
| 11 | 0 | 生日快乐 |
| 12 | 0 | Memory |
| 13 | 0 | 爱的罗曼史 |
| 14 | 0 | Beautiful Dreamer |
| 15 | 0 | Russian Waltz |
| 112 | 0 | 致爱丽丝 |
| 113 | 0 | 黑猫警长 |
| 114 | 0 | 哈农：P21 |
| 1134 | 0 | 海阔天空 |
| 1135 | 0 | 不再犹豫 |
| 1136 | 0 | 灰色轨迹 |
| 1137 | 0 | 长城 |
| 1138 | 0 | 大地 |
| 1139 | 0 | 光辉岁月 |
| 1140 | 0 | 不可一世 |
| 1141 | 0 | 真的爱你 |
| 1142 | 0 | 喜欢你 |
# 希望重新设定这张表f_id的值，令其按照指定字段的数匈奴排列，比如f_id自身，或者f_name
mysql> SET @rn = 0;
Query OK, 0 rows affected (0.00 sec)
# 先把序号写入到表f_catalog中去
mysql> update t_abcjs_staff tas join
-> (SELECT
-> f_id,
-> @rn := @rn + 1 AS new_sort_num -- 每次查询后序号 +1
-> FROM t_abcjs_staff
-> ORDER BY f_id )t2 on tas.f_id = t2.f_id
-> set tas.f_catalog=t2.new_sort_num;
Query OK, 138 rows affected (0.02 sec)
Rows matched: 138 Changed: 138 Warnings: 0
# 看一下
mysql> select f_id,f_catalog,f_name from t_abcjs_staff;
+--------+-----------+--------------------------+
| f_id | f_catalog | f_name |
+--------+-----------+--------------------------+
| 1 | 1 | Allegro in F |
| 2 | 2 | Always with Me |
| 3 | 3 | The Muppet Show Theme |
| 4 | 4 | The Quiet of the Night |
| 5 | 5 | 新年快乐 |
| 6 | 6 | 西班牙斗牛曲 |
| 7 | 7 | Expectation Waltz |
| 8 | 8 | Mosquitto |
| 9 | 9 | Blue Danube |
| 10 | 10 | Merry Christmas |
| 11 | 11 | 生日快乐 |
| 12 | 12 | Memory |
| 13 | 13 | 爱的罗曼史 |
| 14 | 14 | Beautiful Dreamer |
| 15 | 15 | Russian Waltz |
| 112 | 16 | 致爱丽丝 |
| 113 | 17 | 黑猫警长 |
| 114 | 18 | 哈农：P21 |
| 1134 | 19 | 海阔天空 |