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博客

C:\Program Files (x86)\MySQL\MySQL Installer for Windows>MySQLInstallerConsole.exe
=================== Start Initialization ===================
MySQL Installer is running in Community mode

Attempting to update manifest.
Initializing product requirements
Loading product catalog
Checking for product catalog snippets
Checking for product packages in the bundle
Categorizing product catalog
Finding all installed packages.
Your product catalog was last updated at 2020-7-6 11:20:16
=================== End Initialization ===================

Your currently installed community products are:

MySQL Server 8.0.21
         Architecture=X64
         Installed On 2020-12-3
         Install Location: C:\Program Files\MySQL\MySQL Server 8.0\

MySQL Workbench 8.0.21
         Architecture=X64
         Installed On 2020-12-3
         Install Location: C:\Program Files\MySQL\MySQL Workbench 8.0 CE\

MySQL Notifier 1.1.8
         Architecture=X86
         Installed On 2020-12-3
         Install Location: C:\Program Files (x86)\MySQL\MySQL Notifier 1.1

MySQL For Excel 1.3.8
         Architecture=X86
         Installed On 2020-12-3
         Install Location: C:\Program Files (x86)\MySQL\MySQL For Excel 1.3

MySQL for Visual Studio 1.2.9
         Architecture=X86
         Installed On 2020-12-3
         Install Location: C:\Program Files (x86)\MySQL\MySQL for Visual Studio 1.2\

MySQL Shell 8.0.21
         Architecture=X64
         Installed On 2020-12-3
         Install Location: C:\Program Files\MySQL\MySQL Shell 8.0

MySQL Router 8.0.21
         Architecture=X64
         Installed On 2020-12-3
         Install Location: C:\Program Files\MySQL\MySQL Router 8.0\

Connector/ODBC 8.0.21
         Architecture=X64
         Installed On 2020-12-3
         Install Location: C:\Program Files\MySQL\Connector ODBC 8.0\

Connector/C++ 8.0.21
         Architecture=X64
         Installed On 2020-12-3
         Install Location: C:\Program Files\MySQL\Connector C++ 8.0\

Connector/J 8.0.21
         Architecture=X86
         Installed On 2020-12-3
         Install Location: C:\Program Files (x86)\MySQL\Connector J 8.0

Connector/NET 8.0.21
         Architecture=X86
         Installed On 2020-12-3
         Install Location: C:\Program Files (x86)\MySQL\Connector NET 8.0\

Connector/Python 8.0.21
         Architecture=X64
         Installed On 2020-12-3
         Install Location: C:\Program Files\MySQL\Connector Python 8.0

MySQL Documentation 8.0.21
         Architecture=X86
         Installed On 2020-12-3
         Install Location: C:\Program Files (x86)\MySQL\MySQL Documentation 8.0

Samples and Examples 8.0.21
         Architecture=X86
         Installed On 2020-12-3
         Install Location: C:\Program Files (x86)\MySQL\Samples and Examples 8.0\