mysql中怎么存储数组

一、mysql中怎么存储数组

mysql中不能存储数组，但是可以通过传入的字符串截取成多个字符然后传入到临时表中，然后使用游标或者直接关联表过滤数据来储存。

1、创建数据库，就如下图所示一样。

2、根据学生编号批量删除学生信息，如下图所示。

3、声明初始化变量，看到以下画面。

4、获取传入参数数组长度，创建临时表，所输入的如下图所示。

5、截取数组字符串并依次存入到临时表中供后面业务使用。

二、mysql 如何备份和还原数据库

目前 MySQL支持的免费备份工具有：mysqldump、mysqlhotcopy，还可以用 SQL语法进行备份：BACKUP TABLE或者 SELECT INTO OUTFILE，又或者备份二进制日志（binlog），还可以是直接拷贝数据文件和相关的配置文件。MyISAM表是保存成文件的形式，因此相对比较容易备份，上面提到的几种方法都可以使用。Innodb所有的表都保存在同一个数据文件 ibdata1中（也可能是多个文件，或者是独立的表空间文件），相对来说比较不好备份，免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump。

mysqldump是采用SQL级别的备份机制，它将数据表导成 SQL脚本文件，在不同的 MySQL版本之间升级时相对比较合适，这也是最常用的备份方法。现在来讲一下 mysqldump的一些主要参数：

它告诉 mysqldump，导出的数据将和哪种数据库或哪个旧版本的 MySQL服务器相兼容。值可以为 ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options等，要使用几个值，用逗号将它们隔开。当然了，它并不保证能完全兼容，而是尽量兼容。

导出的数据采用包含字段名的完整 INSERT方式，也就是把所有的值都写在一行。这么做能提高插入效率，但是可能会受到 max_allowed_packet参数的影响而导致插入失败。因此，需要谨慎使用该参数，至少我不推荐。

•--default-character-set=charset

指定导出数据时采用何种字符集，如果数据表不是采用默认的 latin1字符集的话，那么导出时必须指定该选项，否则再次导入数据后将产生乱码问题。

告诉 mysqldump在 INSERT语句的开头和结尾增加/*!40000 ALTER TABLE table DISABLE KEYS*/;和/*!40000 ALTER TABLE table ENABLE KEYS*/;语句，这能大大提高插入语句的速度，因为它是在插入完所有数据后才重建索引的。该选项只适合 MyISAM表。

•--extended-insert= true|false

默认情况下，mysqldump开启--complete-insert模式，因此不想用它的的话，就使用本选项，设定它的值为 false即可。

使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用本选项。影响到的字段类型有 BINARY、VARBINARY、BLOB。

在开始导出之前，提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭--single-transaction和--lock-tables选项。

它和--lock-all-tables类似，不过是锁定当前导出的数据表，而不是一下子锁定全部库下的表。本选项只适用于 MyISAM表，如果是 Innodb表可以用--single-transaction选项。

只导出数据，而不添加 CREATE TABLE语句。

不导出任何数据，只导出数据库表结构。

这只是一个快捷选项，等同于同时添加--add-drop-tables--add-locking--create-option--disable-keys--extended-insert--lock-tables--quick--set-charset选项。本选项能让 mysqldump很快的导出数据，并且导出的数据能很快导回。该选项默认开启，但可以用--skip-opt禁用。注意，如果运行 mysqldump没有指定--quick或--opt选项，则会将整个结果集放在内存中。如果导出大数据库的话可能会出现问题。

该选项在导出大表时很有用，它强制 mysqldump从服务器查询取得记录直接输出而不是取得所有记录后将它们缓存到内存中。

该选项在导出数据之前提交一个 BEGIN SQL语句，BEGIN不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于事务表，例如 InnoDB和 BDB。本选项和--lock-tables选项是互斥的，因为 LOCK TABLES会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用--quick选项。

同时导出触发器。该选项默认启用，用--skip-triggers禁用它。

其他参数详情请参考手册，我通常使用以下 SQL来备份 MyISAM表：

/usr/local/mysql/bin/mysqldump-uyejr-pyejr--default-character-set=utf8--opt--extended-insert=false\

--triggers-R--hex-blob-x db_name> db_name.sql

/usr/local/mysql/bin/mysqldump-uyejr-pyejr--default-character-set=utf8--opt--extended-insert=false\

--triggers-R--hex-blob--single-transaction db_name> db_name.sql

用 mysqldump备份出来的文件是一个可以直接倒入的 SQL脚本，有两种方法可以将数据导入。

/usr/local/mysql/bin/mysql-uyejr-pyejr db_name< db_name.sql

其实这不是标准的 SQL语法，而是 mysql客户端提供的功能，例如：

这里需要指定文件的绝对路径，并且必须是 mysqld运行用户(例如 nobody)有权限读取的文件。

mysqlhotcopy是一个 PERL程序，最初由Tim Bunce编写。它使用 LOCK TABLES、FLUSH TABLES和 cp或 scp来快速备份数据库。它是备份数据库或单个表的最快的途径，但它只能运行在数据库文件（包括数据表定义文件、数据文件、索引文件）所在的机器上。mysqlhotcopy只能用于备份 MyISAM，并且只能运行在类Unix和 NetWare系统上。

mysqlhotcopy支持一次性拷贝多个数据库，同时还支持正则表达。以下是几个例子：

root#/usr/local/mysql/bin/mysqlhotcopy-h=localhost-u=yejr-p=yejr db_name/tmp(把数据库目录 db_name拷贝到/tmp下)

root#/usr/local/mysql/bin/mysqlhotcopy-h=localhost-u=yejr-p=yejr db_name_1... db_name_n/tmp

root#/usr/local/mysql/bin/mysqlhotcopy-h=localhost-u=yejr-p=yejr db_name./regex//tmp

更详细的使用方法请查看手册，或者调用下面的命令来查看 mysqlhotcopy的帮助：

perldoc/usr/local/mysql/bin/mysqlhotcopy

注意，想要使用 mysqlhotcopy，必须要有 SELECT、RELOAD(要执行 FLUSH TABLES)权限，并且还必须要能够有读取 datadir/db_name目录的权限。

mysqlhotcopy备份出来的是整个数据库目录，使用时可以直接拷贝到 mysqld指定的 datadir(在这里是/usr/local/mysql/data/)目录下即可，同时要注意权限的问题，如下例：

root#cp-rf db_name/usr/local/mysql/data/

root#chown-R nobody:nobody/usr/local/mysql/data/(将 db_name目录的属主改成 mysqld运行用户)

BACKUP TABLE语法其实和 mysqlhotcopy的工作原理差不多，都是锁表，然后拷贝数据文件。它能实现在线备份，但是效果不理想，因此不推荐使用。它只拷贝表结构文件和数据文件，不同时拷贝索引文件，因此恢复时比较慢。例子：

BACK TABLE tbl_name TO'/tmp/db_name/';

注意，必须要有 FILE权限才能执行本SQL，并且目录/tmp/db_name/必须能被 mysqld用户可写，导出的文件不能覆盖已经存在的文件，以避免安全问题。

SELECT INTO OUTFILE则是把数据导出来成为普通的文本文件，可以自定义字段间隔的方式，方便处理这些数据。例子：

SELECT INTO OUTFILE'/tmp/db_name/tbl_name.txt' FROM tbl_name;

注意，必须要有 FILE权限才能执行本SQL，并且文件/tmp/db_name/tbl_name.txt必须能被 mysqld用户可写，导出的文件不能覆盖已经存在的文件，以避免安全问题。

用 BACKUP TABLE方法备份出来的文件，可以运行 RESTORE TABLE语句来恢复数据表。例子：

RESTORE TABLE FROM'/tmp/db_name/';

用 SELECT INTO OUTFILE方法备份出来的文件，可以运行 LOAD DATA INFILE语句来恢复数据表。例子：

LOAD DATA INFILE'/tmp/db_name/tbl_name.txt' INTO TABLE tbl_name;

权限要求类似上面所述。倒入数据之前，数据表要已经存在才行。如果担心数据会发生重复，可以增加 REPLACE关键字来替换已有记录或者用 IGNORE关键字来忽略他们。

shell> mysqldump--quick db_name| gzip> db_name.contents.gz(该例子中创建的文件是压缩格式）。

shell> gunzip< db_name.contents.gz| mysql db_name以上命令,适用于*nix操作系统的机器

采用 binlog的方法相对来说更灵活，省心省力，而且还可以支持增量备份。

启用 binlog时必须要重启 mysqld。首先，关闭 mysqld，打开 my.cnf，加入以下几行：

然后启动 mysqld就可以了。运行过程中会产生 binlog.000001以及 binlog.index，前面的文件是 mysqld记录所有对数据的更新操作，后面的文件则是所有 binlog的索引，都不能轻易删除。关于 binlog的信息请查看手册。

需要备份时，可以先执行一下 SQL语句，让 mysqld终止对当前 binlog的写入，就可以把文件直接备份，这样的话就能达到增量备份的目的了：

FLUSH LOGS;如果是备份复制系统中的从服务器，还应该备份 master.info和 relay-log.info文件。

备份出来的 binlog文件可以用 MySQL提供的工具 mysqlbinlog来查看，如：

/usr/local/mysql/bin/mysqlbinlog/tmp/binlog.000001

该工具允许你显示指定的数据库下的所有 SQL语句，并且还可以限定时间范围，相当的方便，详细的请查看手册。

恢复时，可以采用类似以下语句来做到：

/usr/local/mysql/bin/mysqlbinlog/tmp/binlog.000001| mysql-uyejr-pyejr db_name

把 mysqlbinlog输出的 SQL语句直接作为输入来执行它。

如果你有空闲的机器，不妨采用这种方式来备份。由于作为 slave的机器性能要求相对不是那么高，因此成本低，用低成本就能实现增量备份而且还能分担一部分数据查询压力，何乐而不为呢？

相较前几种方法，备份数据文件最为直接、快速、方便，缺点是基本上不能实现增量备份。为了保证数据的一致性，需要在靠背文件前，执行以下 SQL语句：

FLUSH TABLES WITH READ LOCK;也就是把内存中的数据都刷新到磁盘中，同时锁定数据表，以保证拷贝过程中不会有新的数据写入。这种方法备份出来的数据恢复也很简单，直接拷贝回原来的数据库目录下即可。

注意，对于 Innodb类型表来说，还需要备份其日志文件，即 ib_logfile*文件。因为当 Innodb表损坏时，就可以依靠这些日志文件来恢复。

对于中等级别业务量的系统来说，备份策略可以这么定：第一次全量备份，每天一次增量备份，每周再做一次全量备份，如此一直重复。而对于重要的且繁忙的系统来说，则可能需要每天一次全量备份，每小时一次增量备份，甚至更频繁。为了不影响线上业务，实现在线备份，并且能增量备份，最好的办法就是采用主从复制机制(replication)，在 slave机器上做备份。

作为一名DBA(我目前还不是，呵呵)，最重要的工作内容之一是保证数据表能安全、稳定、高速使用。因此，需要定期维护你的数据表。以下 SQL语句就很有用：

CHECK TABLE或 REPAIR TABLE，检查或维护 MyISAM表

当然了，上面这些命令起始都可以通过工具 myisamchk来完成，在这里不作详述。

Innodb表则可以通过执行以下语句来整理碎片，提高索引速度：

ALTER TABLE tbl_name ENGINE= Innodb;

这其实是一个 NULL操作，表面上看什么也不做，实际上重新整理碎片了。

通常使用的 MyISAM表可以用上面提到的恢复方法来完成。如果是索引坏了，可以用 myisamchk工具来重建索引。而对于 Innodb表来说，就没这么直接了，因为它把所有的表都保存在一个表空间了。不过 Innodb有一个检查机制叫模糊检查点，只要保存了日志文件，就能根据日志文件来修复错误。可以在 my.cnf文件中，增加以下参数，让 mysqld在启动时自动检查日志文件：

三、如何在mysql 或者 其他的数据库中存放图片 音频 视频

MySQL一直以来都有 TEXT、BLOB等类型用来存储图片、视频等大对象信息。比如一张图片，随便一张都 5M以上。视频也是，随便一部视频就是 2G以上。

假设用 MySQL来存放电影视频等信息，一部是 2G，那么存储 1000部就是 2TB，2TB也就是 1000条记录而已，但是对数据库性能来说，不仅仅是看记录数量，更主要的还得看占用磁盘空间大小。空间大了，所有以前的经验啥的都失效了。

所以一般来说存放这类信息，也就是存储他们的存放路径，至于文件本身存放在哪里，那这就不是数据库考虑的范畴了。数据库只关心怎么来的快，怎么来的小。

虽然不推荐 MySQL这样做，但是也得知道 MySQL该怎么做才行，做到心里有数。比如下面一张微信图片，大概 5M的样子。

root@ytt:/var/lib/mysql-files# ls-sihl微信图片_20190711095019.jpg274501 5.4M-rw-r--r-- 1 root root 5.4M Jul 11 07:17微信图片_20190711095019.jpg

root@ytt:/var/lib/mysql-files# for i in `seq 1 100`; do cp微信图片_20190711095019.jpg"$i".jpg;done;

root@ytt:/var/lib/mysql-files# ls

100.jpg 17.jpg 25.jpg 33.jpg 41.jpg 4.jpg 58.jpg 66.jpg 74.jpg 82.jpg 90.jpg 99.jpg f8.tsv

10.jpg 18.jpg 26.jpg 34.jpg 42.jpg 50.jpg 59.jpg 67.jpg 75.jpg 83.jpg 91.jpg 9.jpg微信图片_20190711095019.jpg

1111.jpg 19.jpg 27.jpg 35.jpg 43.jpg 51.jpg 5.jpg 68.jpg 76.jpg 84.jpg 92.jpg f1.tsv

11.jpg 1.jpg 28.jpg 36.jpg 44.jpg 52.jpg 60.jpg 69.jpg 77.jpg 85.jpg 93.jpg f2.tsv

12.jpg 20.jpg 29.jpg 37.jpg 45.jpg 53.jpg 61.jpg 6.jpg 78.jpg 86.jpg 94.jpg f3.tsv

13.jpg 21.jpg 2.jpg 38.jpg 46.jpg 54.jpg 62.jpg 70.jpg 79.jpg 87.jpg 95.jpg f4.tsv

14.jpg 22.jpg 30.jpg 39.jpg 47.jpg 55.jpg 63.jpg 71.jpg 7.jpg 88.jpg 96.jpg f5.tsv

15.jpg 23.jpg 31.jpg 3.jpg 48.jpg 56.jpg 64.jpg 72.jpg 80.jpg 89.jpg 97.jpg f6.tsv

16.jpg 24.jpg 32.jpg 40.jpg 49.jpg 57.jpg 65.jpg 73.jpg 81.jpg 8.jpg 98.jpg f7.tsv

我们建三张表，分别用 LONGBLOB、LONGTEXT和 VARCHAR来存储这些图片信息

mysql> show create table tt_image1G

*************************** 1. row***************************

Create Table: CREATE TABLE `tt_image1`(

`id` int(11) NOT NULL AUTO_INCREMENT,

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

mysql> show create table tt_image2G

*************************** 1. row***************************

Create Table: CREATE TABLE `tt_image2`(

`id` int(11) NOT NULL AUTO_INCREMENT,

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

mysql> show create table tt_image3G

*************************** 1. row***************************

Create Table: CREATE TABLE `tt_image3`(

`id` int(11) NOT NULL AUTO_INCREMENT,

`image_file` varchar(100) DEFAULT NULL,

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

我们来给三张表插入 100张图片（插入前，建议把 max_allowed_packet设置到最大）

root@ytt:/var/lib/mysql-files# for i in `seq 1 100`;

do mysql-S/var/run/mysqld/mysqld.sock-e"insert into ytt.tt_image1(image_file)

values(load_file('/var/lib/mysql-files/$i.jpg'))";done;

root@ytt:/var/lib/mysql-files# for i in `seq 1 100`;

do mysql-S/var/run/mysqld/mysqld.sock-e"insert into ytt.tt_image2(image_file)

values(hex(load_file('/var/lib/mysql-files/$i.jpg')))";done;

root@ytt:/var/lib/mysql-files# aa='begin;';for i in `seq 1 100`;

do aa=$aa"insert into ytt.tt_image3(image_file) values

('/var/lib/mysql-files/$i.jpg');";

done;aa=$aa'commit;';mysql-S/var/run/mysqld/mysqld.sock-e"`echo$aa`";

看下文件大小，可以看到实际大小排名，LONGTEXT字段存储的最大，LONGBLOB字段缩小到一半，最小的是存储图片路径的表 tt_image3。所以这里从存储空间来看，存放路径最占优势。

tt_image1直接导出来二进制文件即可，下面我写了个存储过程，导出所有图片。

tt_image2类似，把 select语句里 image_file变为 unhex(image_file)即可。

这里我举了个用 MySQL来存放图片的例子，总的来说有以下三点：

占用磁盘空间大（这样会带来各种各样的功能与性能问题，比如备份，写入，读取操作等）

还是推荐用文件路径来代替实际的文件内容存放