王新阳

MYSQL表信息的查看方法是我们经常要实现的功能，下面就为您介绍显示MYSQL表信息的方法，如果您对MYSQL表信息方面感兴趣的话，不妨一看。

在用mysql时(show tables)，有时候需要查看表和字段的相关信息（表与某字段是否存在等.）~~而PHP提供了这样的相关函数，如：mysql_list_dbs（），mysql_list_fields（），但mysql_list_fields()

函数等已过时，所以要用show...这个SQL指令来查寻。

SHOW 指令('[..]'里的可选)

#显示mysql中所有数据库的名称

show databases;

#显示mysql中的相关表

show tables [ from db_name]

#查看存储过程状态

show procedure status

#显示表中列名称

show columns from table_name [ from database_name ] ; 或show columns from

database_name.table_name;

#显示一个用户的权限，显示结果类似于grant 命令

show grants for user_name;

#显示表的索引

show index from table_name

#显示一些系统特定资源的信息，例如，正在运行的线程数量

show status [from db_name]

#显示系统变量的名称和值

show [ global] variables

#显示系统中正在运行的所有进程，也就是当前正在执行的查询大多数用户可以查看他们自己的进程，但是如果他们拥有process权限，就可以查看所有人的进程，包括密码

show processlist

#显示当前使用或者指定的database中的每个表的信息信息包括表类型和表的最新更新时间

show table status

#显示服务器所支持的不同权限

show privileges

#显示create database 语句是否能够创建指定的数据库

show create database database_name

#显示create database 语句是否能够创建指定的数据库

show create table table_name

#显示安装以后可用的存储引擎和默认引擎

show engies

#显示innoDB存储引擎的状态

show innodb status

#显示BDB存储引擎的日志

show logs -----这个已经被遗弃了

#显示最后一个执行的语句所产生的错误、警告和通知

show warnings

#只显示最后一个执行语句所产生的错误

show errors

#显示安装后的可用存储引擎和默认引擎

show [storage] engines;

============================================================

mysqlshow，该指令只参显示数据库、表、列的信息

例如：mysqlshow -uroot -pXXXX mysql #显示mysql数据库的信息

例如：mysqlshow -uroot -pXXXX mysql user #显示mysql数据库中user表的信息

例如：mysqlshow -uroot -pXXXX mysql user user

#显示mysql数据库中user表中的user列的信息

mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication. Please use an administration tool to reset your password with the command SET PASSWORD = PASSWORD('your_existing_password'). This will store a new, and more secure, hash value in mysql.user. If this user is used in other scripts executed by PHP 5.2 or earlier you might need to remove the old-passwords flag from your my.cnf file

解决方法如下：

SET old_passwords = 0;
UPDATE mysql.user SET PASSWORD = PASSWORD( 'root' ) WHERE User = 'root' LIMIT 1 ;
SELECT LENGTH( PASSWORD ) FROM mysql.user WHERE User = 'root';
FLUSH PRIVILEGES ;

对具有相同优先级的运算符，左结合方向意味着将从左向右求值，右结合方向则反之。对于无结合方向具有相同优先级的运算符，该运算符有可能无法与其自身结合。举例说，在 PHP 中 1 < 2 > 1 是一个非法语句，而 1 <= 1 == 1 则不是。因为 T_IS_EQUAL 运算符的优先级比 T_IS_SMALLER_OR_EQUAL 的运算符要低。

新建如下3条：

CREATE TABLE tab2 AS (SELECT * FROM tab1)
这种做法表的存储引擎会采用服务器默认的存储引擎而不是源表的存储引擎，此种复制方法把表的内容也一起复制过来了。

CREATE TALBE tab2 ENGINE=MYISAM, CHARSET='UTF8' AS (SELECT * FROM tab1)
可以自己指定存储引擎和字符集，弥补方法一的不足

CREATE TABLE tab2 LIKE tab1
使用和tab1表相同的结构来创建一个新表，列名、数据类型、空指和索引也将复制，但是表的内容不会被复制。外键和专用的权限也没有被复制。

MySQL复制表结构及数据到新表
CREATE TABLE tab_new SELECT * FROM tab_old

复制旧表的数据到新表(假设两个表结构一样)
INSERT INTO tab1 SELECT * FROM tab2

复制旧表的数据到新表(假设两个表结构不一样)
INSERT INTO tab1(字段1, 字段2, ...) SELECT 字段1, 字段2, ... FROM tab2

更改表名
ALTER TABLE employee RENAME TO staff

更改表注释
ALTER TABLE 表名 COMMENT='新注释';

更改列类型
ALTER TABLE employee MODIFY COLUMN truename VARCHAR(10) NOT NULL DEFAULT ''

修改自增字段下一条记录auto_increment的值为9
ALTER TABLE my_tablename AUTO_INCREMENT=9

修改列注释的时候需要指定{type}
ALTER TABLE student MODIFY COLUMN stu_id INT COMMENT '学号';
参考：http://blog.sina.com.cn/s/blog_72aace390102uwgg.html

更改列名
ALTER TABLE employee CHANGE COLUMN truename employeename VARCHAR(10) NOT NULL DEFAULT ''

添加默认值
ALTER TABLE employee ALTER COLUMN truename SET DEFAULT ''

删除默认值
ALTER TABLE employee ALTER COLUMN truename DEOP DEFAULT

添加列
ALTER TABLE mytable ADD COLUMN addprogram VARCHAR(100) NOT NULL COMMENT '文档添加程序' AFTER title;

删除列
ALTER TABLE mytable DROP COLUMN title

删除索引

ALTER TABLE `table_name` DROP INDEX index_name;

DROP INDEX index_name ON `table_name`;

mysql创建索引
1、添加PRIMARY KEY（主键索引） 
mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 

2、添加UNIQUE(唯一索引) 
mysql>ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 

3、添加INDEX(普通索引) 
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 

4、添加FULLTEXT(全文索引) 
mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 

5、添加多列索引 
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

20240124更新，MYSQL（有多个重复时只保留第一行）：
DELETE FROM my_table WHERE  id NOT IN (SELECT id FROM (SELECT MIN(id) AS id FROM my_table GROUP BY fieldname123) AS a)

20210910更新，MYSQL（只能删除最后一行重复）：
DELETE FROM my_extend_weixin_fans WHERE wxid=123 AND id IN (SELECT id FROM (SELECT MAX(id) AS id FROM my_extend_weixin_fans WHERE wxid=123 GROUP BY openid HAVING COUNT(openid)>1)AS a)

CREATE TABLE mypwd
(id INT UNSIGNED NOT NULL AUTO_INCREMENT,
pwd VARCHAR(14) NOT NULL DEFAULT '',
PRIMARY KEY (id),
UNIQUE KEY pwd(pwd)
)ENGINE=MYISAM, CHARSET='utf8'

INSERT INTO mypwd(pwd) SELECT DISTINCT pwd FROM oldtable

采用这种方式，从已简单处理过的1300多W的表中复制出DISTINCT pwd 1200多W条到mypwd，用时11分39秒，E5400的CPU占用率低于60%，内存占用也很少。
如果不给pwd字段建索引，仅用110秒就复制完，再给pwd字段创建UNIQUE KEY用时9分34秒。
所以用这种方式还是不错的


/**************下面的方法待验证********************/

如果要删除手机(mobilePhone)，电话(officePhone)，邮件(email)同时都相同的数据，以前一直使用这条语句

delete from 表 where id not in (
select max(id) from 表 group by mobilePhone,officePhone,email )

or

delete from 表 where id not in (
select min(id) from 表 group by mobilePhone,officePhone,email )

下面的方法效率更高

效率对比：用delete方法对500万数据去重（1/2重复）约4小时
用临时表插入对500万数据去重（1/2重复）不到10分钟


//查询出唯一数据的ID,并把他们导入临时表tmp中
select min(id) as mid into tmp from 表 group by mobilePhone,officePhone,email

//查询出去重后的数据并插入finally表中
insert into finally select (除ID以外的字段) from customers_1 where id in (select mid from tmp)

UPDATE customer SET customer.amount=(SELECT 
SUM(works_renewals.contractamount) FROM works_renewals 
WHERE customer.cid=works_renewals.cid)

UPDATE customer 
SET customer.photo = COALESCE(
    (SELECT works_renewals.photo FROM works_renewals 
    WHERE works_renewals.customid=customer.id),
    'http://abc.com/photo.jpg'  -- 当子查询返回 NULL 时使用这个默认值
);

MYSQL

UPDATE book SET price=100 WHERE id IN (SELECT id FROM book AS a WHERE a.price=99)

错误代码： 1093
You can't specify target table 'book' for update in FROM clause

mysql同表组合更新时，需要把SELECT出来的数据放到临时表中。SELECT的表和临时表都要有别名。正确语句：

UPDATE book SET price=100 WHERE id IN (SELECT b.id FROM  (SELECT id FROM book AS a WHERE a.price=99) AS b)

MySQL自表更新示例

update
wxy_enum, (select tmp.id, tmp.addfield1 from wxy_enum as tmp) tmp2
set wxy_enum.parentid=tmp2.id
where wxy_enum.addfield2=tmp2.addfield1 and wxy_enum.rootid>0

当表A.name=表B.name时，用 A.id 填充 B.aid

UPDATE tableA
JOIN tableB ON tableA.name = tableB.name
SET tableB.aid = tableA.id;

<?php
//UTF-8转UNICODE
function utf8_unicode($s)
{
	if (is_string($s)) {
		$s = mb_str_split($s);
	}
	$arr = array();
	$n = 0;
	for ($i = 0; $i < count($s); $i++) {
		switch (strlen($s[$i])) {
			case 1:
				$n = ord($s[$i]);
				break;
			case 2:
				$n = (ord($s[$i][0]) & 63) << 6;
				$n += ord($s[$i][1]) & 63;
				break;
			case 3:
				$n = (ord($s[$i][0]) & 31) << 12;
				$n += (ord($s[$i][1]) & 63) << 6;
				$n += ord($s[$i][2]) & 63;
				break;
			case 4:
				$n = (ord($s[$i][0]) & 15) << 18;
				$n += (ord($s[$i][1]) & 63) << 12;
				$n += (ord($s[$i][2]) & 63) << 6;
				$n += ord($s[$i][3]) & 63;
				break;
		}
		$arr[] = $n;
	}
	return $arr;
}
function mb_str_split($str, $charset = 'utf-8', $length = 1)
{
	if ($length < 1) {
		return FALSE;
	}
	$result = array();
	for ($i = 0; $i < mb_strlen($str, $charset); $i += $length) {
		$result[] = mb_substr($str, $i, $length, $charset);
	}
	return $result;
}
//UNICODE转UTF-8
function unicode_utf8($u)
{
	if (is_int($u)) {
		$u = array($u);
	}
	$arr = array();
	$tmp = '';
	for ($i = 0; $i < count($u); $i++) {
		if ($u[$i] < 128) {
			$tmp = $u[$i];
		} else {
			if ($u[$i] < 2048) {
				$tmp = chr(192 | $u[$i] >> 6);
				$tmp .= chr(128 | $u[$i] & 63);
			} else {
				if ($u[$i] < 65536) {
					$tmp = chr(224 | $u[$i] >> 12);
					$tmp .= chr(128 | $u[$i] >> 6 & 63);
					$tmp .= chr(128 | $u[$i] & 63);
				} else {
					if ($u[$i] < 2097152) {
						$tmp = chr(240 | $u[$i] >> 18);
						$tmp .= chr(128 | $u[$i] >> 12 & 63);
						$tmp .= chr(128 | $u[$i] >> 6 & 63);
						$tmp .= chr(128 | $u[$i] & 63);
					}
				}
			}
		}
		$arr[] = $tmp;
	}
	return $arr;
}