原本想导入.sql文件的, 结果输入了二进制文件 .db, 就出现了如下错误.

mysql> source D:/work/cs/Database/sqlite/students/xk2301.db

ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII '\0' is expected. Query: 'SQLite format 3'.

MySQL 中本地数据上传一般可以用 LOAD DATA LOCAL INFILE 命令. 但是 MySQL8 中设置了安全上传目录. 

在登录时可以加上选项 --local-infile=1

即

mysql.exe -uroot -p --local-infile=1

否则使用 load data local infile 'D:\\tmp\\sample.sql' 时会产生错误：

ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

检查变量　ｌｏｃａｌ＿ｉｎｆｉｌｅ

mysql> show variables like '%local_infile%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

(1) 编写函数 factorial(n), 计算 $n!$

DELIMITER //
CREATE FUNCTION factorial(n BIGINT)
RETURNS BIGINT
BEGIN
  DECLARE m BIGINT;
  SET m=1;
  WHILE n>1 
    DO 
    SET m=m*n;
    SET n=n-1;
  END WHILE;
  RETURN m;
END//

mysql> delimiter ;
mysql> SELECT factorial(20);
+---------------------+
| factorial(20)       |
+---------------------+
| 2432902008176640000 |
+---------------------+
1 row in set (0.00 sec)

适用范围

n=1,2,...,20

注意:  参数不能写成 (IN n BIGINT), 否则会返回错误:

ERROR 1064 (42000): Erreur de syntaxe près de 'IN n BIGINT)

验证

可以使用 Calculator 验证

>> 20!
in> 20!

out> 2432902008176640000

------------------------

这部分的信息存储在 information_schema 数据库的表 tables 中.

mysql> SELECT table_catalog, table_schema, table_name, engine
    -> FROM information_schema.tables
    -> WHERE table_schema='Company';
+---------------+--------------+---------------+--------+
| table_catalog | table_schema | table_name    | engine |
+---------------+--------------+---------------+--------+
| def           | company      | department    | MyISAM |
| def           | company      | dependent     | MyISAM |
| def           | company      | dept_location | MyISAM |
| def           | company      | employee      | InnoDB |
| def           | company      | project       | MyISAM |
| def           | company      | works_on      | MyISAM |
+---------------+--------------+---------------+--------+
6 rows in set (0.00 sec)

写一个存储过程, 将Company数据库中所有不是 InnoDB 引擎的数据表都改为 InnoDB 引擎.

Q.  如何将 movies 数据库中的表 MovieStar 中的数据导出为 .csv 格式的文件? 下面的命令可以做到吗?

mysqldump -uroot -p movies MovieStar > D:\tmp\MovieStar.csv

mysqldump.exe --help  可以显示具体用法, 其中如果加选项 -X 或 --xml 则可以导出 XML 文件.

-X, --xml           Dump a database as well formed XML.

但是如果需要导出 csv 格式的数据文件, 则应设置数据间隔符、引用符, 即使用下面的选项, 

--fields-terminated-by=',' --fields-enclosed-by='\"'

substring() 或者 substr() 的原型为:

substring(str, pos, len)

这里 pos 如果设置为 0, 则会返回一个空字符串. 这与C/C++不同, 后者我们通常都认为是从 0 开始.

因此, 若 str="hello", 则要截取前五个字符构成子字符串, 应使用

set @s=substring(str,1,5);

建立一个过程, 不妨命名为 printSchema,  接受两个参数 dbname, tblname. 分别是数据库名和表名. 打印 dbname.tblname 的属性和类型列表, 类似于 DESC dbname.tblname;

delimiter //
CREATE PROCEDURE printSchema(dbname VARCHAR(20), tblname VARCHAR(20))
begin
  SELECT column_name, column_type FROM information_schema.columns WHERE table_schema=dbname and table_name=tblname;
end//

查看数据库sakila中的所有存储过程、函数、触发器

1. 触发器

select * from information_schema.triggers where TRIGGER_SCHEMA='sakila';

2. 存储过程

ROUTINES

进入 sakila 数据库后, 我们一般使用 show tables; 可以看到其中的所有表. 这里认为视图(view)也算表.

mysql> show tables;
+----------------------------+
| Tables_in_sakila           |
+----------------------------+
| actor                      |
| actor_info                 |
| address                    |
| category                   |
| city                       |
| country                    |
| customer                   |
| customer_list              |
| film                       |
| film_actor                 |
| film_category              |
| film_list                  |
| film_text                  |
| inventory                  |
| language                   |
| nicer_but_slower_film_list |
| payment                    |
| rental                     |
| sales_by_film_category     |
| sales_by_store             |
| staff                      |
| staff_list                 |
| store                      |
+----------------------------+
23 rows in set (0.00 sec)

可以看到有 23 张表. 但如果要直接获得 23 这个数字, 该如何查询?

Ans.  数据库的结构一般放在 informaiton_schema 数据库中. 使用下面的查询即可获得数据表的个数.

mysql> select count(table_name) from information_schema.tables where table_schema='sakila';
+-------------------+
| count(table_name) |
+-------------------+
|                23 |
+-------------------+
1 row in set (0.00 sec)

如果使用 SHOW FULL TABLES; 则会显示表的具体属性, 是基础表(BASE TABLE) 还是试图(VIEW).

mysql> show full tables;
+----------------------------+------------+
| Tables_in_sakila           | Table_type |
+----------------------------+------------+
| actor                      | BASE TABLE |
| actor_info                 | VIEW       |
| address                    | BASE TABLE |
| category                   | BASE TABLE |
| city                       | BASE TABLE |
| country                    | BASE TABLE |
| customer                   | BASE TABLE |
| customer_list              | VIEW       |
| film                       | BASE TABLE |
| film_actor                 | BASE TABLE |
| film_category              | BASE TABLE |
| film_list                  | VIEW       |
| film_text                  | BASE TABLE |
| inventory                  | BASE TABLE |
| language                   | BASE TABLE |
| nicer_but_slower_film_list | VIEW       |
| payment                    | BASE TABLE |
| rental                     | BASE TABLE |
| sales_by_film_category     | VIEW       |
| sales_by_store             | VIEW       |
| staff                      | BASE TABLE |
| staff_list                 | VIEW       |
| store                      | BASE TABLE |
+----------------------------+------------+
23 rows in set (0.05 sec)

因此, 如果要列出 sakila 中含有视图的个数, 则使用下面的语句.

mysql> select count(table_name) from information_schema.tables where table_schema='sakila' and table_type='view';
+-------------------+
| count(table_name) |
+-------------------+
|                 7 |
+-------------------+
1 row in set (0.00 sec)

MySQL 8.0 示例数据库 sakila 中有一张表 address, 其中 location 属性是 geometry 类型. 在直接使用 mysqldump.exe 备份时, 比如 mysqldump.exe -uroot -p sakila > sakila.sql

所得到的 sakila.sql 文件中, 在插入 address 数据时, location 部分的数据是二进制类型

例如前三个数据为

INSERT INTO `address` VALUES (1,'47 MySakila Drive',NULL,'Alberta',300,'','',_binary '\0\0\0\0

\0\0\0>\n2]c4\\�vۍ�\�H@','2014-09-25 14:30:27'),(2,'28 MySQL Boulevard',NULL,'QLD',576,'','',_binary '\0\0\0\0

\0\0\0�\�߁$c@N\�P\"�;�','2014-09-25 14:30:09'),(3,'23 Workhaven Lane',NULL,'Alberta',300,'','14033335568',_binary '\0\0\0\0

\0\0\0\�\�hc4\\�\�~p�\�H@','2014-09-25 14:30:27');

这在导入数据库时, 会发生错误. 提示

Error Code: 1416. Cannot get geometry object from data you send to the GEOMETRY field

现在加入 --hex-blob 选项

"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqldump.exe" --hex-blob -uroot -p -P 3308 sakila > sakila_new.sql

如果要导入到低版本的MySQL 中, 比如 MySQL 5.7.23, 则建议将所得的 sakila_new.sql 中的 utf8mb4 改为 utf8,  校对字符集 utf8mb4_0900_ai_ci 改为 utf8_general_ci.

当然, 相应的, 首先在 MySQL 5.7.23 中建立数据库 sakila, 字符集使用 utf8, 校对字符集使用 utf8_general_ci.

附 mysqldump.exe 的基本使用方法:

Usage: mysqldump [OPTIONS] database [tables]

OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help

References:

[1] mysql - Back up a table with a GEOMETRY column using mysqldump? - Stack Overflow

[2] MySQL Bugs: #43544: mysqldump should convert GEOMETRY columns to hex with --hex-blob option

[3] OGR2OGR Cheatsheet (bostongis.com)

[4] mysql设置了utf8mb4还是报错_升级MySQL 8.0的诡异故障，utf8mb4_0900_ai_ci是什么？_编程小族管的博客-CSDN博客