php插入mysql数据避免重复插入

作者:admin 日期:2012-04-24

PHP取得当前插入数据库中最新记录的id值

作者:admin 日期:2011-04-05

Mysql之mysqldump工具

作者:admin 日期:2022-05-18

一、mysqldump工具简介

mysqldump是mysql用于转存储数据库的实用程序。它主要产生一个SQL脚本，其中包含从头重新创建数据库所必需的命令Create TABLE Insert等。它的备份原理是通过协议连接到 MySQL 数据库，将需要备份的数据查询出来，将查询出的数据转换成对应的insert 语句，当我们需要还原这些数据时，只要执行这些 insert 语句，即可将对应的数据还原。

二、使用示例

当前数据库清单如下：

mysql> show databases;

±-------------------+

| Database |

±-------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

| test1 |

| test2 |

| test3 |

±-------------------+

7 rows in set (0.00 sec)

1、备份实例下的所有库

使用–all-databases 或 -A 导出全部数据库

[root@test2 backuptest]# mysqldump -uroot -p -A > all.sql

Enter password:

[root@test2 backuptest]# ll -h

total 875M

-rw-r–r-- 1 root root 875M Feb 9 11:06 all.sql

[root@test2 backuptest]# cat all.sql |grep “Current Database:”

– Current Database: mysql

– Current Database: test1

– Current Database: test2

– Current Database: test3

2、导出单个指定数据库

[root@test2 backuptest]# mysqldump -uroot -p test1 > test1.sql

Enter password:

[root@test2 backuptest]# ll -h

total 875M

-rw-r–r-- 1 root root 875M Feb 9 11:06 all.sql

-rw-r–r-- 1 root root 425K Feb 9 11:29 test1.sql

3、导出多个数据库

使用 --databases参数同时导出多个数据库

[root@test2 backuptest]# mysqldump -uroot -p --databases test1 test2 > 2.sql

Enter password:

[root@test2 backuptest]# ll -h

total 1003M

-rw-r–r-- 1 root root 99M Feb 9 11:30 2.sql

-rw-r–r-- 1 root root 875M Feb 9 11:06 all.sql

-rw-r–r-- 1 root root 425K Feb 9 11:29 test1.sql

[root@test2 backuptest]# cat 2.sql |grep “Current Database:”

– Current Database: test1

– Current Database: test2

4、导出单张表

[root@test2 backuptest]# mysqldump -uroot -p mysql user > mysql.user.sql;

Enter password:

[root@test2 backuptest]# ll -h

total 1003M

-rw-r–r-- 1 root root 99M Feb 9 11:30 2.sql

-rw-r–r-- 1 root root 875M Feb 9 11:06 all.sql

-rw-r–r-- 1 root root 5.6K Feb 9 11:32 mysql.user.sql

-rw-r–r-- 1 root root 425K Feb 9 11:29 test1.sql

5、导出多张表

[root@test2 backuptest]# mysqldump -uroot -p mysql user db > t2.sql;

Enter password:

[root@test2 backuptest]# ll -h

total 1003M

-rw-r–r-- 1 root root 99M Feb 9 11:30 2.sql

-rw-r–r-- 1 root root 875M Feb 9 11:06 all.sql

-rw-r–r-- 1 root root 5.6K Feb 9 11:32 mysql.user.sql

-rw-r–r-- 1 root root 7.9K Feb 9 11:33 t2.sql

-rw-r–r-- 1 root root 425K Feb 9 11:29 test1.sql

6、导出匹配行

使用–where参数导出匹配行，条件内容必须加引号，如果导出数据需要导入其他的表，建议加上–skip-add-drop-table参数，因为mysqldump默认导出时添加drop table语句。

[root@test2 backuptest]# mysqldump -uroot -p --databases mysql --tables user --where=“user=‘root’” --skip-add-drop-table > user.root.sql

Enter password:

[root@test2 backuptest]# ll -h

total 974M

-rw-r–r-- 1 root root 99M Feb 9 11:30 2.sql

-rw-r–r-- 1 root root 875M Feb 9 11:06 all.sql

-rw-r–r-- 1 root root 5.6K Feb 9 11:32 mysql.user.sql

-rw-r–r-- 1 root root 7.9K Feb 9 11:33 t2.sql

-rw-r–r-- 1 root root 425K Feb 9 11:29 test1.sql

-rw-r–r-- 1 root root 5.0K Feb 9 11:42 user.root.sql

7、只导出表结构

使用-d 或 --no-data 参数导出数据库表结构

[root@test2 backuptest]# mysqldump -uroot -p --all-databases --no-data > all.d.sql

Enter password:

[root@test2 backuptest]# ll -h

total 974M

-rw-r–r-- 1 root root 99M Feb 9 11:30 2.sql

-rw-r–r-- 1 root root 131K Feb 9 13:43 all.d.sql

-rw-r–r-- 1 root root 875M Feb 9 11:06 all.sql

-rw-r–r-- 1 root root 5.6K Feb 9 11:32 mysql.user.sql

-rw-r–r-- 1 root root 7.9K Feb 9 11:33 t2.sql

-rw-r–r-- 1 root root 425K Feb 9 11:29 test1.sql

-rw-r–r-- 1 root root 5.0K Feb 9 11:42 user.root.sql

8、带库名导出

使用-B参数带创建库语句的备份文件

[root@test2 backuptest]# mysqldump -uroot -p -B test1 > test1.name.sql

Enter password:

[root@test2 backuptest]# cat test1.name.sql |grep “Create DATABASE”

Create DATABASE /!32312 IF NOT EXISTS/ test1 /*!40100 DEFAULT CHARACTER SET utf8mb4 */;

9、跨服务器导出

[root@test1 backuptest]# mysqldump -uroot -p -h 192.168.0.125 test1 > s125.test1.sql

Enter password:

[root@test1 backuptest]# ll -h

total 428K

-rw-r–r--. 1 root root 425K Feb 9 13:46 s125.test1.sql

10、跨服务器导出时启用压缩传递所有信息

[root@test1 backuptest]# mysqldump -uroot -p -h 192.168.0.125 -C test2 > s125.test2.sql

Enter password:

[root@test1 backuptest]# ll -h

total 99M

-rw-r–r--. 1 root root 425K Feb 9 13:46 s125.test1.sql

-rw-r–r--. 1 root root 98M Feb 9 13:49 s125.test2.sql

三、使用样式及参数说明

1、语法样式

用法: mysqldump [OPTIONS] database [tables]

或 mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3…]

或 mysqldump [OPTIONS] --all-databases [OPTIONS]

2、参数详解

–print-defaults

-打印默认值打印程序参数列表并退出。

–no-defaults

无默认值不从任何选项文件中读取默认选项，

除了登录文件。

–defaults file=#

仅从给定文件#读取默认选项。

–defaults-extra-file=#

读取全局文件后读取此文件。

–defaults-group-suffix=#

同时读取带有concat（group，后缀）的组

–login-path=#

从登录文件中读取此路径。

–all-databases , -A

导出全部数据库。

mysqldump -uroot -p --all-databases

–all-tablespaces , -Y

导出全部表空间。

mysqldump -uroot -p --all-databases --all-tablespaces

–no-tablespaces , -y

不导出任何表空间信息。

mysqldump -uroot -p --all-databases --no-tablespaces

–add-drop-database

每个数据库创建之前添加drop数据库语句。

mysqldump -uroot -p --all-databases --add-drop-database

–add-drop-table

每个数据表创建之前添加drop数据表语句。(默认为打开状态，使用–skip-add-drop-table取消选项)

mysqldump -uroot -p --all-databases (默认添加drop语句)

mysqldump -uroot -p --all-databases –skip-add-drop-table (取消drop语句)

–add-locks

在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(默认为打开状态，使用–skip-add-locks取消选项)

mysqldump -uroot -p --all-databases (默认添加LOCK语句)

mysqldump -uroot -p --all-databases –skip-add-locks (取消LOCK语句)

–allow-keywords

允许创建是关键词的列名字。这由表名前缀于每个列名做到。

mysqldump -uroot -p --all-databases --allow-keywords

–apply-slave-statements

在’CHANGE MASTER’前添加’STOP SLAVE’，并且在导出的最后添加’START SLAVE’。

mysqldump -uroot -p --all-databases --apply-slave-statements

–character-sets-dir

字符集文件的目录

mysqldump -uroot -p --all-databases --character-sets-dir=/usr/local/mysql/share/mysql/charsets

–comments

附加注释信息。默认为打开，可以用–skip-comments取消

mysqldump -uroot -p --all-databases (默认记录注释)

mysqldump -uroot -p --all-databases --skip-comments (取消注释)

–compatible

导出的数据将和其它数据库或旧版本的MySQL 相兼容。值可以为ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options等，

要使用几个值，用逗号将它们隔开。它并不保证能完全兼容，而是尽量兼容。

mysqldump -uroot -p --all-databases --compatible=ansi

–compact

导出更少的输出信息(用于调试)。去掉注释和头尾等结构。可以使用选项：–skip-add-drop-table --skip-add-locks --skip-comments --skip-disable-keys

mysqldump -uroot -p --all-databases --compact

–complete-insert, -c

使用完整的insert语句(包含列名称)。这么做能提高插入效率，但是可能会受到max_allowed_packet参数的影响而导致插入失败。

mysqldump -uroot -p --all-databases --complete-insert

–compress, -C

在客户端和服务器之间启用压缩传递所有信息

mysqldump -uroot -p --all-databases --compress

–create-options, -a

在Create TABLE语句中包括所有MySQL特性选项。(默认为打开状态)

mysqldump -uroot -p --all-databases

–databases, -B

导出几个数据库。参数后面所有名字参量都被看作数据库名。

mysqldump -uroot -p --databases test mysql

–debug

输出debug信息，用于调试。默认值为：d:t:o,/tmp/mysqldump.trace

mysqldump -uroot -p --all-databases --debug

mysqldump -uroot -p --all-databases --debug=” d:t:o,/tmp/debug.trace”

–debug-check

检查内存和打开文件使用说明并退出。

mysqldump -uroot -p --all-databases --debug-check

–debug-info

输出调试信息并退出

mysqldump -uroot -p --all-databases --debug-info

–default-character-set

设置默认字符集，默认值为utf8

mysqldump -uroot -p --all-databases --default-character-set=latin1

–delayed-insert

采用延时插入方式（Insert DELAYED）导出数据

mysqldump -uroot -p --all-databases --delayed-insert

–delete-master-logs

master备份后删除日志. 这个参数将自动激活–master-data。

mysqldump -uroot -p --all-databases --delete-master-logs

–disable-keys

对于每个表，用/*!40000 Alter TABLE tbl_name DISABLE KEYS /;和/!40000 Alter TABLE tbl_name ENABLE KEYS */;语句引用Insert语句。这样可以更快地导入dump出来的文件，因为它是在插入所有行后创建索引的。该选项只适合MyISAM表，默认为打开状态。

mysqldump -uroot -p --all-databases

–dump-slave

该选项将导致主的binlog位置和文件名追加到导出数据的文件中。设置为1时，将会以CHANGE MASTER命令输出到数据文件；设置为2时，在命令前增加说明信息。该选项将会打开–lock-all-tables，除非–single-transaction被指定。该选项会自动关闭–lock-tables选项。默认值为0。

mysqldump -uroot -p --all-databases --dump-slave=1

mysqldump -uroot -p --all-databases --dump-slave=2

–events, -E

导出事件。

mysqldump -uroot -p --all-databases --events

–extended-insert, -e

使用具有多个VALUES列的Insert语法。这样使导出文件更小，并加速导入时的速度。默认为打开状态，使用–skip-extended-insert取消选项。

mysqldump -uroot -p --all-databases

mysqldump -uroot -p --all-databases–skip-extended-insert (取消选项)

–fields-terminated-by

导出文件中忽略给定字段。与–tab选项一起使用，不能用于–databases和–all-databases选项

mysqldump -uroot -p test test --tab=”/home/mysql” --fields-terminated-by=”#”

–fields-enclosed-by

输出文件中的各个字段用给定字符包裹。与–tab选项一起使用，不能用于–databases和–all-databases选项

mysqldump -uroot -p test test --tab=”/home/mysql” --fields-enclosed-by=”#”

–fields-optionally-enclosed-by

输出文件中的各个字段用给定字符选择性包裹。与–tab选项一起使用，不能用于–databases和–all-databases选项

mysqldump -uroot -p test test --tab=”/home/mysql” --fields-enclosed-by=”#” --fields-optionally-enclosed-by =”#”

–fields-escaped-by

输出文件中的各个字段忽略给定字符。与–tab选项一起使用，不能用于–databases和–all-databases选项

mysqldump -uroot -p mysql user --tab=”/home/mysql” --fields-escaped-by=”#”

–flush-logs

开始导出之前刷新日志。

请注意：假如一次导出多个数据库(使用选项–databases或者–all-databases)，将会逐个数据库刷新日志。除使用–lock-all-tables或者–master-data外。在这种情况下，日志将会被刷新一次，相应的所以表同时被锁定。因此，如果打算同时导出和刷新日志应该使用–lock-all-tables 或者–master-data 和–flush-logs。

mysqldump -uroot -p --all-databases --flush-logs

–flush-privileges

在导出mysql数据库之后，发出一条FLUSH PRIVILEGES 语句。为了正确恢复，该选项应该用于导出mysql数据库和依赖mysql数据库数据的任何时候。

mysqldump -uroot -p --all-databases --flush-privileges

–force

在导出过程中忽略出现的SQL错误。

mysqldump -uroot -p --all-databases --force

–help

显示帮助信息并退出。

mysqldump --help

–hex-blob

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

mysqldump -uroot -p --all-databases --hex-blob

–host, -h

需要导出的主机信息

mysqldump -uroot -p --host=localhost --all-databases

–ignore-table

不导出指定表。指定忽略多个表时，需要重复多次，每次一个表。每个表必须同时指定数据库和表名。例如：–ignore-table=database.table1 --ignore-table=database.table2 ……

mysqldump -uroot -p --host=localhost --all-databases --ignore-table=mysql.user

–include-master-host-port

在–dump-slave产生的’CHANGE MASTER TO…‘语句中增加’MASTER_HOST=，MASTER_PORT=’

mysqldump -uroot -p --host=localhost --all-databases --include-master-host-port

–insert-ignore

在插入行时使用Insert IGNORE语句.

mysqldump -uroot -p --host=localhost --all-databases --insert-ignore

–lines-terminated-by

输出文件的每行用给定字符串划分。与–tab选项一起使用，不能用于–databases和–all-databases选项。

mysqldump -uroot -p --host=localhost test test --tab=”/tmp/mysql” --lines-terminated-by=”##”

–lock-all-tables, -x

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

mysqldump -uroot -p --host=localhost --all-databases --lock-all-tables

–lock-tables, -l

开始导出前，锁定所有表。用READ LOCAL锁定表以允许MyISAM表并行插入。对于支持事务的表例如InnoDB和BDB，–single-transaction是一个更好的选择，因为它根本不需要锁定表。

请注意当导出多个数据库时，–lock-tables分别为每个数据库锁定表。因此，该选项不能保证导出文件中的表在数据库之间的逻辑一致性。不同数据库表的导出状态可以完全不同。

mysqldump -uroot -p --host=localhost --all-databases --lock-tables

–log-error

附加警告和错误信息到给定文件

mysqldump -uroot -p --host=localhost --all-databases --log-error=/tmp/mysqldump_error_log.err

–master-data

该选项将binlog的位置和文件名追加到输出文件中。如果为1，将会输出CHANGE MASTER 命令；如果为2，输出的CHANGE MASTER命令前添加注释信息。该选项将打开–lock-all-tables 选项，除非–single-transaction也被指定（在这种情况下，全局读锁在开始导出时获得很短的时间；其他内容参考下面的–single-transaction选项）。该选项自动关闭–lock-tables选项。

mysqldump -uroot -p --host=localhost --all-databases --master-data=1;

mysqldump -uroot -p --host=localhost --all-databases --master-data=2;

–max_allowed_packet

服务器发送和接受的最大包长度。

mysqldump -uroot -p --host=localhost --all-databases --max_allowed_packet=10240

–net_buffer_length

TCP/IP和socket连接的缓存大小。

mysqldump -uroot -p --host=localhost --all-databases --net_buffer_length=1024

–no-autocommit

使用autocommit/commit 语句包裹表。

mysqldump -uroot -p --host=localhost --all-databases --no-autocommit

–no-create-db, -n

只导出数据，而不添加Create DATABASE 语句。

mysqldump -uroot -p --host=localhost --all-databases --no-create-db

–no-create-info, -t

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

mysqldump -uroot -p --host=localhost --all-databases --no-create-info

–no-data, -d

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

mysqldump -uroot -p --host=localhost --all-databases --no-data

–no-set-names, -N

等同于–skip-set-charset

mysqldump -uroot -p --host=localhost --all-databases --no-set-names

–opt

等同于–add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, --disable-keys 该选项默认开启, 可以用–skip-opt禁用.

mysqldump -uroot -p --host=localhost --all-databases --opt

–order-by-primary

如果存在主键，或者第一个唯一键，对每个表的记录进行排序。在导出MyISAM表到InnoDB表时有效，但会使得导出工作花费很长时间。

mysqldump -uroot -p --host=localhost --all-databases --order-by-primary

–password, -p

连接数据库密码

–pipe(windows系统可用)

使用命名管道连接mysql

mysqldump -uroot -p --host=localhost --all-databases --pipe

–port, -P

连接数据库端口号

–protocol

使用的连接协议，包括：tcp, socket, pipe, memory.

mysqldump -uroot -p --host=localhost --all-databases --protocol=tcp

–quick, -q

不缓冲查询，直接导出到标准输出。默认为打开状态，使用–skip-quick取消该选项。

mysqldump -uroot -p --host=localhost --all-databases

mysqldump -uroot -p --host=localhost --all-databases --skip-quick

–quote-names,-Q

使用（`）引起表和列名。默认为打开状态，使用–skip-quote-names取消该选项。

mysqldump -uroot -p --host=localhost --all-databases

mysqldump -uroot -p --host=localhost --all-databases --skip-quote-names

–replace

使用REPLACE INTO 取代Insert INTO.

mysqldump -uroot -p --host=localhost --all-databases --replace

–result-file, -r

直接输出到指定文件中。该选项应该用在使用回车换行对（\r\n）换行的系统上（例如：DOS，Windows）。该选项确保只有一行被使用。

mysqldump -uroot -p --host=localhost --all-databases --result-file=/tmp/mysqldump_result_file.txt

–routines, -R

导出存储过程以及自定义函数。

mysqldump -uroot -p --host=localhost --all-databases --routines

–set-charset

添加’SET NAMES default_character_set’到输出文件。默认为打开状态，使用–skip-set-charset关闭选项。

mysqldump -uroot -p --host=localhost --all-databases

mysqldump -uroot -p --host=localhost --all-databases --skip-set-charset

–single-transaction

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

mysqldump -uroot -p --host=localhost --all-databases --single-transaction

–dump-date

将导出时间添加到输出文件中。默认为打开状态，使用–skip-dump-date关闭选项。

mysqldump -uroot -p --host=localhost --all-databases

mysqldump -uroot -p --host=localhost --all-databases --skip-dump-date

–skip-opt

禁用–opt选项.

mysqldump -uroot -p --host=localhost --all-databases --skip-opt

–socket,-S

指定连接mysql的socket文件位置，默认路径/tmp/mysql.sock

mysqldump -uroot -p --host=localhost --all-databases --socket=/tmp/mysqld.sock

–tab,-T

为每个表在给定路径创建tab分割的文本文件。注意：仅仅用于mysqldump和mysqld服务器运行在相同机器上。

mysqldump -uroot -p --host=localhost test test --tab="/home/mysql"

–tables

覆盖–databases (-B)参数，指定需要导出的表名。

mysqldump -uroot -p --host=localhost --databases test --tables test

–triggers

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

mysqldump -uroot -p --host=localhost --all-databases --triggers

–tz-utc

在导出顶部设置时区TIME_ZONE=’+00:00’ ，以保证在不同时区导出的TIMESTAMP 数据或者数据被移动其他时区时的正确性。

mysqldump -uroot -p --host=localhost --all-databases --tz-utc

–user, -u

指定连接的用户名。

–verbose, --v

输出多种平台信息。

–version, -V

输出mysqldump版本信息并退出

–where, -w

只转储给定的Where条件选择的记录。请注意如果条件包含命令解释符专用空格或字符，一定要将条件引用起来。

mysqldump -uroot -p --host=localhost --all-databases --where=” user=’root’”

–xml, -X

导出XML格式.

mysqldump -uroot -p --host=localhost --all-databases --xml

–plugin_dir

客户端插件的目录，用于兼容不同的插件版本。

mysqldump -uroot -p --host=localhost --all-databases --plugin_dir=”/usr/local/lib/plugin”

–default_auth

客户端插件默认使用权限。

mysqldump -uroot -p --host=localhost --all-databases --default-auth=”/usr/local/lib/plugin/”

Tags: mysql

分类:技术文章 | 固定链接 | 评论: 0 | 引用: 0 | 查看次数: 207

Wordpress数据恢复用到的一些代码

作者:admin 日期:2022-01-06

接前一篇文章：mysql根据.frm和.ibd文件恢复表结构和数据

数据库只要.ibd文件完整就可以恢复数据，.frm文件不需要。

数据库建表：

--
-- 表的结构 `wp_commentmeta`
--

Tags: wordpress mysql

分类:技术文章 | 固定链接 | 评论: 0 | 引用: 0 | 查看次数: 68

LNMP一键安装包安装的mysql远程连接不上的问题

作者:admin 日期:2022-01-03

正常的做法:

　　以root用户登录mysql:

　　　　grant all privileges on *.* to 创建的用户名@"%" identified by "密码";

　　　　flush privileges;

　　例如: 　　　　

　　　　mysql>grant all privileges on *.* to zhangsan@"%" identified by "123456";

　　　　mysql>flush privileges;

　　这里就是允许zhangsan用户远程登录, 密码是123456

但是使用后发现还是远程连接不上, 原因是为了安全, lnmp禁止远程连接

　　查看已有iptables规则, 用序号显示

　　iptables -L -n --line-numbers

　　删除对应的drop规则

　　iptables -D INPUT 6

　　然后远程连接, 成功连接　　

Tags: lnmp mysql

分类:技术文章 | 固定链接 | 评论: 0 | 引用: 0 | 查看次数: 77

Mysql错误（Incorrect key file for table）try to repair

作者:admin 日期:2022-01-03

mysql错误：mysql需要建立临时文件，建立失败

错误原因：内存不足/无权限

Tags: mysql

分类:技术文章 | 固定链接 | 评论: 0 | 引用: 0 | 查看次数: 51

mysql根据.frm和.ibd文件恢复表结构和数据

作者:admin 日期:2022-01-03

以下内容未转载，因为我用的是wordpress，所有表结构都有，所以直接使用了黄底蓝字部分，成功恢复！原文作者少了必须的第4步，害我多花了很多时间-_-||。

这篇内容有些长，如果你急着恢复数据，直接略过前面关于数据库信息的一些解释。

在恢复数据之前，先来了解几个基本问题：

1、Mysql/Mariadb 的数据库引擎有好几个，常见或者常用的是：MYISAM和InnoDB，这个在新建数据库的时候就可以选择。

Tags: centos mysql

分类:技术文章 | 固定链接 | 评论: 0 | 引用: 0 | 查看次数: 74

Linux服务器上如何设置MySQL的max_allowed_packet

作者:admin 日期:2019-02-13

MySQL上传文件到数据时文件太大会提示max_allowed_packet错误！

查看当前位置：

show global VARIABLES like '%max_allowed_packet%';

修改配置：

Tags: mysql centos

分类:技术文章 | 固定链接 | 评论: 0 | 引用: 0 | 查看次数: 446

mysql改变字符串的大小写转换

作者:admin 日期:2019-01-06

INITCAP:转换每个字的第一个字符为大写

LOWER:转换所有字符为小写

UPPER:转换所有字符为人写

eg:

LOWER(phone)

Tags: mysql

分类:技术文章 | 固定链接 | 评论: 0 | 引用: 0 | 查看次数: 447

Mysql常用命令行大全

作者:admin 日期:2018-06-12

1、连接Mysql

格式： mysql -h主机地址 -u用户名－p用户密码

1、连接到本机上的MYSQL。
首先打开DOS窗口，然后进入目录mysql\bin，再键入命令mysql -u root -p，回车后提示你输密码.注意用户名前可以有空格也可以没有空格，但是密码前必须没有空格，否则让你重新输入密码。

如果刚安装好MYSQL，超级用户root是没有密码的，故直接回车即可进入到MYSQL中了，MYSQL的提示符是： mysql>

2、连接到远程主机上的MYSQL。假设远程主机的IP为：110.110.110.110，用户名为root,密码为abcd123。则键入以下命令：
mysql -h110.110.110.110 -u root -p 123;（注:u与root之间可以不用加空格，其它也一样）

3、退出MYSQL命令： exit （回车）

2、修改密码

格式：mysqladmin -u用户名 -p旧密码 password 新密码

1、给root加个密码ab12。
首先在DOS下进入目录mysql\bin，然后键入以下命令
mysqladmin -u root -password ab12
注：因为开始时root没有密码，所以-p旧密码一项就可以省略了。

2、再将root的密码改为djg345。
mysqladmin -u root -p ab12 password djg345

3、增加新用户

注意：和上面不同，下面的因为是MYSQL环境中的命令，所以后面都带一个分号作为命令结束符

格式：grant select on 数据库.* to 用户名@登录主机 identified by “密码”

1、增加一个用户test1密码为abc，让他可以在任何主机上登录，并对所有数据库有查询、插入、修改、删除的权限。首先用root用户连入MYSQL，然后键入以下命令：
    grant select,insert,update,delete on *.* to [email=test1@”%]test1@”%[/email]” Identified by “abc”;

但增加的用户是十分危险的，你想如某个人知道test1的密码，那么他就可以在internet上的任何一台电脑上登录你的mysql数据库并对你的数据可以为所欲为了，解决办法见2。

2、增加一个用户test2密码为abc,让他只可以在localhost上登录，并可以对数据库mydb进行查询、插入、修改、删除的操作（localhost指本地主机，即MYSQL数据库所在的那台主机），这样用户即使用知道test2的密码，他也无法从internet上直接访问数据库，只能通过MYSQL主机上的web页来访问了。
    grant select,insert,update,delete on mydb.* to [email=test2@localhost]test2@localhost[/email] identified by “abc”;

如果你不想test2有密码，可以再打一个命令将密码消掉。
    grant select,insert,update,delete on mydb.* to [email=test2@localhost]test2@localhost[/email] identified by “”;

4.1 创建数据库

注意：创建数据库之前要先连接Mysql服务器

命令：create database <数据库名>

例1：建立一个名为xhkdb的数据库
mysql> create database xhkdb;

例2：创建数据库并分配用户

①Create DATABASE 数据库名;

②GRANT Select,Insert,Update,Delete,Create,Drop,Alter ON 数据库名.* TO 数据库名@localhost IDENTIFIED BY '密码';

③SET PASSWORD FOR '数据库名'@'localhost' = OLD_PASSWORD('密码');

依次执行3个命令完成数据库创建。注意：中文 “密码”和“数据库”是户自己需要设置的。

4.2 显示数据库

命令：show databases （注意：最后有个s）
mysql> show databases;

注意：为了不再显示的时候乱码，要修改数据库默认编码。以下以GBK编码页面为例进行说明：

1、修改MYSQL的配置文件：my.ini里面修改default-character-set=gbk
2、代码运行时修改：
①Java代码：jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=gbk
②PHP代码：header("Content-Type:text/html;charset=gb2312");
③C语言代码：int mysql_set_character_set( MYSQL * mysql, char * csname)；
该函数用于为当前连接设置默认的字符集。字符串csname指定了1个有效的字符集名称。连接校对成为字符集的默认校对。该函数的工作方式与SET NAMES语句类似，但它还能设置mysql- > charset的值，从而影响了由mysql_real_escape_string() 设置的字符集。

4.3 删除数据库

命令：drop database <数据库名>
例如：删除名为 xhkdb的数据库
mysql> drop database xhkdb;

例子1：删除一个已经确定存在的数据库
mysql> drop database drop_database;
Query OK, 0 rows affected (0.00 sec)

例子2：删除一个不确定存在的数据库
mysql> drop database drop_database;
ERROR 1008 (HY000): Can't drop database 'drop_database'; database doesn't exist
//发生错误，不能删除'drop_database'数据库，该数据库不存在。
mysql> drop database if exists drop_database;
Query OK, 0 rows affected, 1 warning (0.00 sec)//产生一个警告说明此数据库不存在
mysql> create database drop_database;
Query OK, 1 row affected (0.00 sec)
mysql> drop database if exists drop_database;//if exists 判断数据库是否存在，不存在也不产生错误
Query OK, 0 rows affected (0.00 sec)

4.4 连接数据库

命令： use <数据库名>

例如：如果xhkdb数据库存在，尝试存取它：
mysql> use xhkdb;
屏幕提示：Database changed

use 语句可以通告MySQL把db_name数据库作为默认（当前）数据库使用，用于后续语句。该数据库保持为默认数据库，直到语段的结尾，或者直到发布一个不同的USE语句：
mysql> USE db1;
mysql> Select COUNT(*) FROM mytable;   # selects from db1.mytable
mysql> USE db2;
mysql> Select COUNT(*) FROM mytable;   # selects from db2.mytable

使用USE语句为一个特定的当前的数据库做标记，不会阻碍您访问其它数据库中的表。下面的例子可以从db1数据库访问作者表，并从db2数据库访问编辑表：
mysql> USE db1;
mysql> Select author_name,editor_name FROM author,db2.editor
->        Where author.editor_id = db2.editor.editor_id;

USE语句被设立出来，用于与Sybase相兼容。

有些网友问到，连接以后怎么退出。其实，不用退出来，use 数据库后，使用show databases就能查询所有数据库，如果想跳到其他数据库，用
use 其他数据库名字
就可以了。

4.5 当前选择的数据库

命令：mysql> select database();

MySQL中Select命令类似于其他编程语言里的print或者write，你可以用它来显示一个字符串、数字、数学表达式的结果等等。如何使用MySQL中Select命令的特殊功能？

1.显示MYSQL的版本
mysql> select version();
+-----------------------+
| version()             |
+-----------------------+
| 6.0.4-alpha-community |
+-----------------------+
1 row in set (0.02 sec)

2. 显示当前时间
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2009-09-15 22:35:32 |
+---------------------+
1 row in set (0.04 sec)

3. 显示年月日
Select DAYOFMONTH(CURRENT_DATE);
+--------------------------+
| DAYOFMONTH(CURRENT_DATE) |
+--------------------------+
|                       15 |
+--------------------------+
1 row in set (0.01 sec)

Select MONTH(CURRENT_DATE);
+---------------------+
| MONTH(CURRENT_DATE) |
+---------------------+
|                   9 |
+---------------------+
1 row in set (0.00 sec)

Select YEAR(CURRENT_DATE);
+--------------------+
| YEAR(CURRENT_DATE) |
+--------------------+
|               2009 |
+--------------------+
1 row in set (0.00 sec)

4. 显示字符串
mysql> Select "welecome to my blog!";
+----------------------+
| welecome to my blog! |
+----------------------+
| welecome to my blog! |
+----------------------+
1 row in set (0.00 sec)

5. 当计算器用
select ((4 * 4) / 10 ) + 25;
+----------------------+
| ((4 * 4) / 10 ) + 25 |
+----------------------+
|                26.60 |
+----------------------+
1 row in set (0.00 sec)

6. 串接字符串
select CONCAT(f_name, " ", l_name)
AS Name
from employee_data
where title = 'Marketing Executive';
+---------------+
| Name          |
+---------------+
| Monica Sehgal |
| Hal Simlai    |
| Joseph Irvine |
+---------------+
3 rows in set (0.00 sec)
注意：这里用到CONCAT()函数，用来把字符串串接起来。另外，我们还用到以前学到的AS给结果列'CONCAT(f_name, " ", l_name)'起了个假名。

5.1 创建数据表

命令：create table <表名> ( <字段名1> <类型1> [,..<字段名n> <类型n>]);

例如，建立一个名为MyClass的表，

字段名	数字类型	数据宽度	是否为空	是否主键	自动增加	默认值
id	int	4	否	primary key	auto_increment
name	char	20	否
sex	int	4	否			0
degree	double	16	是

mysql> create table MyClass(
> id int(4) not null primary key auto_increment,
> name char(20) not null,
> sex int(4) not null default '0',
> degree double(16,2));

5.3 删除数据表

命令：drop table <表名>

例如：删除表名为 MyClass 的表
mysql> drop table MyClass;

Drop TABLE用于取消一个或多个表。您必须有每个表的Drop权限。所有的表数据和表定义会被取消，所以使用本语句要小心！

注意：对于一个带分区的表，Drop TABLE会永久性地取消表定义，取消各分区，并取消储存在这些分区中的所有数据。Drop TABLE还会取消与被取消的表有关联的分区定义（.par）文件。

对与不存在的表，使用IF EXISTS用于防止错误发生。当使用IF EXISTS时，对于每个不存在的表，会生成一个NOTE。

RESTRICT和CASCADE可以使分区更容易。目前，RESTRICT和CASCADE不起作用。

5.4 表插入数据

命令：insert into <表名> [( <字段名1>[,..<字段名n > ])] values ( 值1 )[, ( 值n )]

例如：往表 MyClass中插入二条记录, 这二条记录表示：编号为1的名为Tom的成绩为96.45, 编号为2 的名为Joan 的成绩为82.99，编号为3 的名为Wang 的成绩为96.5。
mysql> insert into MyClass values(1,'Tom',96.45),(2,'Joan',82.99), (2,'Wang', 96.59);

注意：insert into每次只能向表中插入一条记录。

5.5 查询表中的数据

1)、查询所有行
命令： select <字段1，字段2，...> from < 表名 > where < 表达式 >
例如：查看表 MyClass 中所有数据
mysql> select * from MyClass;

2）、查询前几行数据
例如：查看表 MyClass 中前2行数据
mysql> select * from MyClass order by id limit 0,2;

select一般配合where使用，以查询更精确更复杂的数据。

5.6 删除表中数据

命令：delete from 表名 where 表达式

例如：删除表 MyClass中编号为1 的记录
mysql> delete from MyClass where id=1;

下面是一个删除数据前后表的对比。

FirstName	LastName	Age
Peter	Griffin	35
Glenn	Quagmire	33

下面以PHP代码为例删除 "Persons" 表中所有 LastName='Griffin' 的记录：

<?php 
   $con = mysql_connect("localhost","peter","abc123"); 
   if (!$con) 
   {
      die('Could not connect: ' . mysql_error()); 
   } 
   mysql_select_db("my_db", $con); 
   mysql_query("Delete FROM Persons Where LastName='Griffin'"); mysql_close($con); 
?>

在这次删除之后，表是这样的：

FirstName	LastName	Age
Glenn	Quagmire	33

5.7 修改表中数据

语法：update 表名 set 字段=新值,… where 条件
mysql> update MyClass set name='Mary' where id=1;

例子1：单表的MySQL Update语句：
Update [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [Where where_definition] [ORDER BY ...] [LIMIT row_count]

例子2：多表的Update语句：
Update [LOW_PRIORITY] [IGNORE] table_references SET col_name1=expr1 [, col_name2=expr2 ...] [Where where_definition]

Update语法可以用新值更新原有表行中的各列。SET子句指示要修改哪些列和要给予哪些值。Where子句指定应更新哪些行。如果没有Where子句，则更新所有的行。如果指定了ORDER BY子句，则按照被指定的顺序对行进行更新。LIMIT子句用于给定一个限值，限制可以被更新的行的数目。

5.8 增加字段

命令：alter table 表名 add字段类型其他;
例如：在表MyClass中添加了一个字段passtest，类型为int(4)，默认值为0
mysql> alter table MyClass add passtest int(4) default '0'

加索引
mysql> alter table 表名 add index 索引名 (字段名1[，字段名2 …]);
例子： mysql> alter table employee add index emp_name (name);

加主关键字的索引
mysql> alter table 表名 add primary key (字段名);
例子： mysql> alter table employee add primary key(id);

加唯一限制条件的索引
mysql> alter table 表名 add unique 索引名 (字段名);
例子： mysql> alter table employee add unique emp_name2(cardnumber);

删除某个索引
mysql> alter table 表名 drop index 索引名;
例子： mysql>alter table employee drop index emp_name;

增加字段：
mysql> Alter TABLE table_name ADD field_name field_type;

修改原字段名称及类型：
mysql> Alter TABLE table_name CHANGE old_field_name new_field_name field_type;

删除字段：
MySQL Alter TABLE table_name Drop field_name;

5.9 修改表名

命令：rename table 原表名 to 新表名;

例如：在表MyClass名字更改为YouClass
mysql> rename table MyClass to YouClass;

当你执行 RENAME 时，你不能有任何锁定的表或活动的事务。你同样也必须有对原初表的 Alter 和 Drop 权限，以及对新表的 Create 和 Insert 权限。

如果在多表更名中，MySQL 遭遇到任何错误，它将对所有被更名的表进行倒退更名，将每件事物退回到最初状态。

RENAME TABLE 在 MySQL 3.23.23 中被加入。

6、备份数据库

命令在DOS的[url=file://\\mysql\\bin]\\mysql\\bin[/url]目录下执行

1.导出整个数据库
导出文件默认是存在mysql\bin目录下
    mysqldump -u 用户名 -p 数据库名 > 导出的文件名
    mysqldump -u user_name -p123456 database_name > outfile_name.sql

2.导出一个表
    mysqldump -u 用户名 -p 数据库名表名> 导出的文件名
    mysqldump -u user_name -p database_name table_name > outfile_name.sql

3.导出一个数据库结构
    mysqldump -u user_name -p -d –add-drop-table database_name > outfile_name.sql
    -d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table

4.带语言参数导出
    mysqldump -uroot -p –default-character-set=latin1 –set-charset=gbk –skip-opt database_name > outfile_name.sql

例如，将aaa库备份到文件back_aaa中：
[root@test1 root]# cd　/home/data/mysql
[root@test1 mysql]# mysqldump -u root -p --opt aaa > back_aaa

7.1 一个建库和建表的实例1

drop database if exists school; //如果存在SCHOOL则删除
create database school; //建立库SCHOOL
use school; //打开库SCHOOL
create table teacher //建立表TEACHER
(
    id int(3) auto_increment not null primary key,
    name char(10) not null,
    address varchar(50) default ‘深圳’,
    year date
); //建表结束

//以下为插入字段
insert into teacher values(”,’allen’,'大连一中’,'1976-10-10′);
insert into teacher values(”,’jack’,'大连二中’,'1975-12-23′);

如果你在mysql提示符键入上面的命令也可以，但不方便调试。
1、你可以将以上命令原样写入一个文本文件中，假设为school.sql，然后复制到c:\\下，并在DOS状态进入目录[url=file://\\mysql\\bin]\\mysql\\bin[/url]，然后键入以下命令：
    mysql -uroot -p密码 < c:\\school.sql
如果成功，空出一行无任何显示；如有错误，会有提示。（以上命令已经调试，你只要将//的注释去掉即可使用）。

2、或者进入命令行后使用 mysql> source c:\\school.sql; 也可以将school.sql文件导入数据库中。

7.2 一个建库和建表的实例2

drop database if exists school; //如果存在SCHOOL则删除
create database school; //建立库SCHOOL
use school; //打开库SCHOOL
create table teacher //建立表TEACHER
(
id int(3) auto_increment not null primary key,
    name char(10) not null,
    address varchar(50) default ''深圳'',
    year date
); //建表结束

//以下为插入字段
insert into teacher values('''',''glchengang'',''深圳一中'',''1976-10-10'');
insert into teacher values('''',''jack'',''深圳一中'',''1975-12-23'');

注：在建表中
1、将ID设为长度为3的数字字段:int(3)；并让它每个记录自动加一:auto_increment；并不能为空:not null；而且让他成为主字段primary key。

2、将NAME设为长度为10的字符字段

3、将ADDRESS设为长度50的字符字段，而且缺省值为深圳。

4、将YEAR设为日期字段。

Tags: mysql

分类:技术文章 | 固定链接 | 评论: 0 | 引用: 0 | 查看次数: 319

使用MySQL命令行备份和恢复数据库

作者:admin 日期:2018-06-12

导出数据库：

1、windows下cmd打开命令行

2、cd 到MySQL的安装目录的bin 目录，如果bin目录添加到环境变量中不需要切换到bin目录

3、导出数据库：mysqldump -u 用户名 -p 数据库名 > 导出的文件名

比如在命令行中输入命令 mysqldump -u root -p person >d:\person.sql (输入后会让你输入进入MySQL的密码)
（如果导出单张表的话在数据库名后面输入表名即可）

4、执行完成后，会看到d盘中有person.sql 文件

MySQL命令行导入数据库
1、进入MySQL：mysql -u 用户名 -p
如我输入的命令行:mysql -u root -p (输入同样后会让你输入MySQL的密码)
2、新建一个数据库，这时是空数据库，如新建一个名为person的目标数据库
3，输入：mysql>use 目标数据库名
如我输入的命令行:mysql>use person;
4，导入文件：mysql>source 导入的文件名;
如我输入的命令行：mysql>source d:/person.sql;

Tags: mysql

分类:技术文章 | 固定链接 | 评论: 0 | 引用: 0 | 查看次数: 329

解决MySql error #1036 Table is read only 错误方法

作者:admin 日期:2017-07-20

linux中解决办法

1. 找到数据库文件放置的目录，如/usr/local/mysql/var/xx

2. 更改此数据库下所有文件的所有权：chown mysql.mysql *.*

3. 关闭mysql：killall mysqld

4. 开启mysql:进入mysql的bin目录，运行 ./mysqld_safe --user=mysql &

3、4步我用lnmp mysql restart代替的

Tags: mysql

分类:技术文章 | 固定链接 | 评论: 0 | 引用: 0 | 查看次数: 594

作者:admin 日期:2012-04-24

作者:admin 日期:2011-04-05

作者:admin 日期:2022-05-18

作者:admin 日期:2022-01-06

作者:admin 日期:2022-01-03

作者:admin 日期:2022-01-03

作者:admin 日期:2022-01-03

作者:admin 日期:2019-02-13

作者:admin 日期:2019-01-06

作者:admin 日期:2018-06-12

1、连接Mysql

2、修改密码

3、增加新用户

4.1 创建数据库

4.2 显示数据库

4.3 删除数据库

4.4 连接数据库

4.5 当前选择的数据库

5.1 创建数据表

5.3 删除数据表

5.4 表插入数据

5.5 查询表中的数据

5.6 删除表中数据

5.7 修改表中数据

5.8 增加字段

5.9 修改表名

6、备份数据库

7.1 一个建库和建表的实例1

7.2 一个建库和建表的实例2

作者:admin 日期:2018-06-12

作者:admin 日期:2017-07-20

博客公告

博客搜索

用户面板

最新日志

最新评论

日志分类

博客日历

博客统计

最新留言

日志归档

友情链接

相关支持

赞助商位置