Skip to content

Sql

IP地址在mysql的存储(IP地址和int的转换)

文章作者:Enjoy 转载请注明原文链接。

PHP
echo ip2long(‘192.168.1.38’);
输出:3232235814

MYSQL
SELECT INET_ATON(‘192.168.1.38’);
输出:3232235814

两个函数返回的结果是一样的,都是A*256*256*256+B*256*256+C*256+D的算法
192*256*256*256+168*256*256+1*256+38 = 3 232 235 814

反过来,从int转换为IP地址分别是php的long2ip()和mysql的INET_NTOA()。

mysql存储这个值是字段需要用int UNSIGNED。不用UNSIGNED的话,128以上的IP段就存储不了了。

传统的方法,创建varchar(15),需要占用15个字节,而改时使用int只需要4字节,可以省一些字节。

php存入时:$ip = ip2long($ip);
mysql取出时:SELECT INET_ATON(ip) FROM table …
php取出时,多一步:$ip = long2ip($ip);

转换以前的数据:

1.把以前的varchar()数据转换为int型的SQL语句:
UPDATE `hx_table` SET ip =  INET_ATON(ip) WHERE INET_ATON(ip) is NOT NULL
2.把字段更改为int型:
ALTER TABLE `hx_table` CHANGE `ip` `ip` INT UNSIGNED NOT NULL
3.程序做相应修改上传,完成。

@@UPDATE@@20110310:

在32位的机子上,echo ip2long(‘192.168.1.38’);由于超过32位的最大数,导致输出负数-1062731482。

有两种方法更新为正数:
$ip_long = bindec(decbin(ip2long($ip)));

$ip_long = = sprintf(“%u”, ip2long($ip));
因此一种是修改PHP程序,使其肯定存入正数。
另一种是将mysql的这个字段使用int,非UNSIGNED,使其可以存入负数。

MS SQL SERVER 数据字典 生成SQL

在库内查询,可用于生成如下字段结构:

[表], [字段], [数据类型], [允许为空], [主键], [外键], [引用表], [说明描述]

Mr_Tang_1438232827174_74

 

SELECT TOP (100) PERCENT a.name AS 表, b.name AS 字段, c.name AS 数据类型, CASE WHEN b.isnullable = 0 THEN '-' WHEN b.isnullable = 1 THEN '是' END AS 允许为空,
CASE WHEN d .name IS NULL THEN '-' WHEN d .name IS NOT NULL THEN '是' END AS 主键, CASE WHEN e.parent_object_id IS NULL THEN 0 ELSE 1 END AS 外键,
CASE WHEN e.parent_object_id IS NULL THEN '-' ELSE g.name END AS 引用表, CASE WHEN h.value IS NULL THEN '-' ELSE h.value END AS 说明描述,
i.text AS 默认值
FROM sys.sysobjects AS a INNER JOIN
sys.syscolumns AS b ON a.id = b.id INNER JOIN
sys.systypes AS c ON b.xtype = c.xtype LEFT OUTER JOIN
(SELECT so.id, sc.colid, sc.name
FROM sys.syscolumns AS sc INNER JOIN
sys.sysobjects AS so ON so.id = sc.id INNER JOIN
sys.sysindexkeys AS si ON so.id = si.id AND sc.colid = si.colid
WHERE (si.indid = 1)) AS d ON a.id = d.id AND b.colid = d.colid LEFT OUTER JOIN
sys.foreign_key_columns AS e ON a.id = e.parent_object_id AND b.colid = e.parent_column_id LEFT OUTER JOIN
sys.objects AS g ON e.referenced_object_id = g.object_id LEFT OUTER JOIN
sys.extended_properties AS h ON a.id = h.major_id AND b.colid = h.minor_id LEFT OUTER JOIN
sys.syscomments AS i ON b.cdefault = i.id
WHERE (a.type = 'U') AND (c.name <> 'sysname')
ORDER BY 表, 字段

 

还有一个查全库表字段的,参见另一篇 《通过(sys.sysobjects)对象表,查询全库表的基础信息Sql Server 查询全库表信息

MySql中表名使用双下划线”__”,phpMyAdmin中将以前缀分组显示

很久没有更新过新版的phpMyAdmin了,前些天装了个版本: 4.0.10.9,提示最新版本已经是: 4.4.4.

启用 phpMyAdmin 高级功能,导入 create_tables.sql 时,难得细心看了下sql,发现里面的表名都是两个“__”下划线,以前是一个的,如下:

  • `pma__history`
  • `pma__pdf_pages`
  • `pma__######`

导入后在phpMyAdmin中发现,数据库中“pma”开头的表可以分组收缩。(如同)

20150501223253

 

忽然明白,表面中的双下划线“__”就是让MySql数据库内的表名进行分组的依据, “__ ”之前的字符标志就是分组名称。

于是把自己有些乱的库中表名也进行了前缀分组。

 

P.S. 不知道这个双下划线”__”前缀分组的功能是MySql的原生的功能,还是phpMyAdmin自定义的,我想应该是phpMyAdmin自己定义功能。

 

VPS下对mysql内存性能的优化

VPS的内存对性能至关重要,所以很有必要优化一下。

看了几篇针对vps小内存优化的文章,于是自己也动手参照优化下自己的mysql。

修改过调整好的 my.cnf ,service mysqld restart 启动时报错。

查看log,发现 skip-innodb / skip-bdb / skip-locking,这几个参数有问题。

 

继续学习,查资料,得知 使用的参数都是老版本的参数,mysql5.1对应的为:

skip-innodb  –>  loose-skip-innodb

skip-locking  –> skip-external-locking

skip-bdb (已经废除了skip-bdb这个参数!)

 

配置文件: /etc/my.cnf

# low memory stuff – Mr.Tang
# Tue May 26 22:23:15 CST 2015

[mysqld]

loose-skip-innodb
skip-external-locking
skip-host-cache
skip-name-resolve

character_set_server = utf8
default-storage-engine = myisam

key_buffer_size = 256M
key_buffer = 256K
max_allowed_packet = 1M
myisam_sort_buffer_size = 8M
net_buffer_length = 128K
query_cache_size= 16M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
sort_buffer_size = 1M
table_cache = 4M
table_open_cache = 16M
thread_cache_size = 8M
thread_stack = 131072

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer = 8M
sort_buffer_size = 8M

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

SQL 增加自动增长id列

方法1:

在表设计器中手动创建列,选择标识,种子,增量。

20140624230538

 

方法2:

直接建表是创建生成

CREATE TABLE Tab_Test (ID int IDENTITY (1, 1), Name varchar(50))

 

方法3:

修改以有的表,增加一列

ALTER TABLE _TAB_Epm_Users ADD nID int IDENTITY (1, 1)

 

方法4:

用存储过程完成, 我没用过。自己查下。

 

方法5:

其他方法, 也可以变通完成。

 

比如:在插入数据时可以 先 MAX(nID)  原主键,然后+1后插入。

比如:已有一张表,我想在表里增加一个自动增长列,但是我想添进去的自动增长列按关键字order by排序,有简单易行的方法吗

——解决方案——————–
select NewID = identity(int,1,1), * into NewTable from OldTable order by 旧的数据的编号按关键字排序
——解决方案——————–
用2005的排序函数 row_number()
select row_number() over (order by id), * from sysobjects

些许会常用到的sql语句

这里记录些许个会经常用到的sql语句。

 

P.S.   [Your_Table]  = 你的表名, [nid] = 你表中的主键唯一列.

/*按分页数量取表记录中的数据*/
/*以nid排序, 取20条之后的10条*/SELECT TOP 10 *
FROM Your_Table
WHERE (nid NOT IN
          (SELECT TOP 20 nid
         FROM Your_Table
         ORDER BY nid))
ORDER BY nid
/*取传入条件记录的前一条数据*/
/*取 nid='533' 记录的前一条, 返回记录 nid='532'*/
SELECT *
FROM Your_Table
WHERE (nid =
          (SELECT MAX(nid)
         FROM Your_Table
         WHERE nid < 533))
/*取传入条件记录的后一条数据*/
/*取 nid='533' 记录的后一条, 返回记录 nid='532'*/		 
SELECT *
FROM Your_Table
WHERE (nid =
          (SELECT MIN(nid)
         FROM Your_Table
         WHERE nid > 534))
		 
		 

 

分页存储过程,SQL 2000 数据库下使用的:

-- =============================================

-- DocumentName       分页存储过程 SQL 2000数据库下使用的

-- 描述               利用SQL查询语句进行分页

-- 输入

--       {

--        @SQL            :    SQL查询语句,示例:'Select * from [TableName]'

--        @Order        :    排序,示例:[ColumnName] [ASC | DESC]

--        @CurPage        :    当前页,示例:0..9

--        @PageRows        :    每页显示的行数,示例:0..9

--        @TotalRecorder:    查询记录总数(输出参数)

--          @IsXML        :    表示返回的结果,0表示以表格形式记录,1表示以XML格式返回记录;默认为0

--       }

-- =============================================



ALTER PROCEDURE [dbo].[SeparatePage]

    -- Add the parameters for the stored procedure here

    @SQL Nvarchar(2000),

    @Order Nvarchar(20),

    @CurPage int,

    @PageRows int,

    @TotalRecorder int output,

    @IsXML bit = 0

AS

BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    SET NOCOUNT ON;

    declare @ExceSQL nvarchar(4000)

    

    --设置开始行号

    declare  @start_row_num AS int

    SET @start_row_num = (@CurPage - 1) * @PageRows

   

    if @CurPage > 1 

        BEGIN

            SET @start_row_num = @start_row_num + 1;

            SET @PageRows = @PageRows - 1

        END

    else

        SET @start_row_num = @start_row_num

    

    --获取总记录数

    set @ExceSQL = 'SELECT tb.* into [tb_Temp] FROM ('+ @SQL +') tb ORDER BY'+ @order

    execute(@ExceSQL)

    SELECT @TotalRecorder = COUNT(*) from [tb_Temp]



    ALTER TABLE [tb_Temp] add [RowNumber] int



    --设置查询语句

    SET @ExceSQL = 'SELECT TOP '+ convert(varchar(10),@PageRows) +' * 

                    FROM (SELECT TOP '+ convert(varchar(10),@PageRows) +' * 

                    FROM (SELECT TOP '+ convert(varchar(10),@TotalRecorder-@start_row_num+1) +' *

                    FROM [tb_Temp] ORDER BY '+ REPLACE(@Order,'DESC','ASC') +') AS a

                    ORDER BY '+ REPLACE(@Order,'ASC','DESC') +') AS b'



    IF(@IsXML = 1)

        SET @ExceSQL = @ExceSQL + ' FOR XML AUTO,ELEMENTS'

 

    EXECUTE(@ExceSQL)

    DROP Table [tb_Temp]

END

 

分页存储过程,SQL 2005 数据库下使用的:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go


-- =============================================

-- DocumentName       分页存储过程  SQL 2005数据库下使用

-- 描述                 利用SQL查询语句进行分页

-- 输入

--       {

--        @SQL            :    SQL查询语句,示例:'Select * from [TableName]'

--        @Order        :    排序,示例:[ColumnName] [ASC | DESC]

--        @CurPage        :    当前页,示例:0..9

--        @PageRows        :    每页显示的行数,示例:0..9

--        @TotalRecorder:    查询记录总数(输出参数)

--          @IsXML        :    表示返回的结果,0表示以表格形式记录,1表示以XML格式返回记录;默认为0

--       }

-- =============================================



ALTER PROCEDURE [dbo].[SeparatePage]

    -- Add the parameters for the stored procedure here

    @SQL Nvarchar(2000),

    @Order Nvarchar(20),

    @CurPage int,

    @PageRows int,

    @TotalRecorder int output,

    @IsXML bit = 0

AS

BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    SET NOCOUNT ON;

    declare @ExceSQL nvarchar(4000)

    

    --设置开始行号

    declare  @start_row_num AS int

    SET @start_row_num = (@CurPage - 1) * @PageRows

   

    if @CurPage > 1 

        BEGIN

            SET @start_row_num = @start_row_num + 1;

            SET @PageRows = @PageRows - 1

        END

    else

        SET @start_row_num = @start_row_num

    

    --设置标签语句

    declare @RowNumber nvarchar(100)

    set @RowNumber = ', ROW_NUMBER() OVER(ORDER BY ' + @Order + ') as RowNumber from '



    set @SQL = Replace(@SQL,' from ',@RowNumber)



    --获取总记录数

    set @ExceSQL = 'WITH [TempTable] AS (' + @SQL + ') 

        select @TotalRecorder=max(RowNumber) from [TempTable]'



    execute sp_executesql @ExceSQL,N'@TotalRecorder int output',@TotalRecorder output



    --设置查询语句

    set @ExceSQL = 'WITH [TempTable] AS (' + @SQL + ') 

        select * from [TempTable] where RowNumber between ' + Convert(nvarchar,@start_row_num)

        + ' And ' + Convert(nvarchar,@start_row_num+@PageRows)

    

    IF(@IsXML = 1)SET @ExceSQL = @ExceSQL + 'FOR XML AUTO,ELEMENTS'

 

    execute(@ExceSQL)



END

 

查询获取表中, 字段,数据类型的sql

存档一个今天用到的sql,  可以用来查询表中, 字段, 数据类型的sql.

SELECT OBJECT_NAME(c.id) AS 表名, c.name AS 字段名, t.name AS 数据类型, 
      c.prec AS 长度
FROM syscolumns c INNER JOIN
      systypes t ON c.xusertype = t.xusertype
WHERE (objectproperty(c.id, 'IsUserTable') = 1) AND 
      (c.id = OBJECT_ID('你的表名'))

 

如果要查视图的话, 要修改一下where条件,  把where的objectproperty放到上面增加一个case判断,  AS 出类型

SELECT     CASE WHEN (OBJECTPROPERTY(c.id, 'IsUserTable') = 1) THEN 'Table' WHEN (OBJECTPROPERTY(c.id, 'IsView') = 1) THEN 'View' END AS 类型, OBJECT_NAME(c.id) 
                      AS 表名, c.name AS 字段名, t.name AS 数据类型, c.prec AS 长度
FROM         sys.syscolumns AS c INNER JOIN
                      sys.systypes AS t ON c.xusertype = t.xusertype
WHERE     (c.id = OBJECT_ID('View_1'))

(OBJECTPROPERTY(c.id, ‘IsUserTable’) = 1) 是表,  (OBJECTPROPERTY(c.id, ‘IsView’) = 1) 是视图

关于 OBJECTPROPERTY 的详细用法和参数, 请查看: http://technet.microsoft.com/zh-cn/library/ms176105(v=sql.90).aspx

推荐一个正则表达式测试/验证工具

这几天代码里用到很多正则表达式,需要验证,直接在程序里调试太麻烦。

比如:C#中用正则表达式取页面下拉菜单(select)中的值 – http://as32.net/blog/8815

 

找到了这个验证工具:

正则表达式测试器 – http://deerchao.net/tools/regex_tester/index.htm
说明:该工具允许你测试和分析正则表达式。

Sql时间转varchar字符串

 

CONVERT(data_type,expression_r_r[,style])

语句及查询结果:

SELECT CONVERT(varchar(100), GETDATE_r(), 0): 05 16 2006 10:57AM

SELECT CONVERT(varchar(100), GETDATE_r(), 1): 05/16/06

SELECT CONVERT(varchar(100), GETDATE_r(), 2): 06.05.16

SELECT CONVERT(varchar(100), GETDATE_r(), 3): 16/05/06

SELECT CONVERT(varchar(100), GETDATE_r(), 4): 16.05.06

SELECT CONVERT(varchar(100), GETDATE_r(), 5): 16-05-06

SELECT CONVERT(varchar(100), GETDATE_r(), 6): 16 05 06

SELECT CONVERT(varchar(100), GETDATE_r(), 7): 05 16, 06

SELECT CONVERT(varchar(100), GETDATE_r(), 8): 10:57:46

SELECT CONVERT(varchar(100), GETDATE_r(), 9): 05 16 2006 10:57:46:827AM

SELECT CONVERT(varchar(100), GETDATE_r(), 10): 05-16-06

SELECT CONVERT(varchar(100), GETDATE_r(), 11): 06/05/16

SELECT CONVERT(varchar(100), GETDATE_r(), 12): 060516

SELECT CONVERT(varchar(100), GETDATE_r(), 13): 16 05 2006 10:57:46:937

SELECT CONVERT(varchar(100), GETDATE_r(), 14): 10:57:46:967

SELECT CONVERT(varchar(100), GETDATE_r(), 20): 2006-05-16 10:57:47

SELECT CONVERT(varchar(100), GETDATE_r(), 21): 2006-05-16 10:57:47.157

SELECT CONVERT(varchar(100), GETDATE_r(), 22): 05/16/06 10:57:47 AM

SELECT CONVERT(varchar(100), GETDATE_r(), 23): 2006-05-16

SELECT CONVERT(varchar(100), GETDATE_r(), 24): 10:57:47

SELECT CONVERT(varchar(100), GETDATE_r(), 25): 2006-05-16 10:57:47.250

SELECT CONVERT(varchar(100), GETDATE_r(), 100): 05 16 2006 10:57AM

SELECT CONVERT(varchar(100), GETDATE_r(), 101): 05/16/2006

SELECT CONVE

Sql Server 查询全库表信息

通过(sys.sysobjects)对象表,查询全库表的基础信息 :

SELECT name AS 对象名, info AS 字段数, 
      CASE WHEN xtype = 'C' THEN 'CHECK 约束' WHEN xtype = 'D' THEN '默认值约束' WHEN
       xtype = 'F' THEN '外键约束' WHEN xtype = 'L' THEN '日志' WHEN xtype = 'FN' THEN '标量函数'
       WHEN xtype = 'IF' THEN '内嵌表函数' WHEN xtype = 'P' THEN '存储过程' WHEN xtype
       = 'PK' THEN '主键约束(类型是 K)' WHEN xtype = 'RF' THEN '复制筛选存储过程' WHEN
       xtype = 'S' THEN '系统表' WHEN xtype = 'TF' THEN '表函数' WHEN xtype = 'TR' THEN
       '触发器' WHEN xtype = 'U' THEN '用户表' WHEN xtype = 'UQ' THEN '唯一约束(类型是 K)'
       WHEN xtype = 'V' THEN '视图' WHEN xtype = 'X' THEN '扩展存储过程' END AS 对象类型,
       CASE WHEN ftcatid = 0 THEN '否' WHEN ftcatid = 1 THEN '是' END AS 索引, 
      crdate AS 创建日期, CONVERT(char(19), getdate(), 120) AS 本次索引时间
FROM sysobjects
ORDER BY name

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

这里查询的包含了系统表,如果要过滤掉系统表请加上where条件!

如只要用户表和用户视图: WHERE   (xtype = ‘U’) or (xtype = ‘V’)

xtype 可以是以下对象类型之一:

AF = 聚合函数 (CLR)

C = CHECK 约束

D = 默认值或 DEFAULT 约束

F = FOREIGN KEY 约束

L = 日志

FN = 标量函数

FS = 程序集 (CLR) 标量函数

FT = 程序集 (CLR) 表值函数

IF = 内联表函数

IT = 内部表

P = 存储过程

PC = 程序集 (CLR) 存储过程

PK = PRIMARY KEY 约束(类型为 K)

RF = 复制筛选存储过程

S = 系统表

SN = 同义词

SQ = 服务队列

TA = 程序集 (CLR) DML 触发器

TF = 表函数

TR = SQL DML 触发器

TT = 表类型

U = 用户表

UQ = UNIQUE 约束(类型为 K)

V = 视图

X = 扩展存储过程

 

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

重要提示: 将此 SQL Server 2000 系统表作为一个视图包含进来是为了保持向后兼容性。建议您改用当前的 SQL Server 系统视图。若要查找一个或多个等效系统视图,请参阅将 SQL Server 2000 系统表映射到 SQL Server 2005 系统视图。后续版本的 Microsoft SQL Server 将删除该功能。请避免在新的开发工作中使用该功能,并着手修改当前还在使用该功能的应用程序。

列名 数据类型 说明

name

sysname

对象名

id

int

对象标识号

xtype

char(2)

对象类型。可以是以下对象类型之一:

AF = 聚合函数 (CLR)

C = CHECK 约束

D = 默认值或 DEFAULT 约束

F = FOREIGN KEY 约束

L = 日志

FN = 标量函数

FS = 程序集 (CLR) 标量函数

FT = 程序集 (CLR) 表值函数

IF = 内联表函数

IT = 内部表

P = 存储过程

PC = 程序集 (CLR) 存储过程

PK = PRIMARY KEY 约束(类型为 K)

RF = 复制筛选存储过程

S = 系统表

SN = 同义词

SQ = 服务队列

TA = 程序集 (CLR) DML 触发器

TF = 表函数

TR = SQL DML 触发器

TT = 表类型

U = 用户表

UQ = UNIQUE 约束(类型为 K)

V = 视图

X = 扩展存储过程

uid

smallint

对象所有者的架构 ID。

对于从旧版 SQL Server 升级的数据库,架构 ID 等于所有者的用户 ID。

ms177596.note(zh-cn,SQL.105).gif重要提示: 如果使用以下任何一个 SQL Server DDL 语句,则必须使用 sys.objects 目录视图而不是 sys.sysobjects。 CREATE | ALTER | DROP USER CREATE | ALTER | DROP ROLE CREATE | ALTER | DROP APPLICATION ROLE CREATE SCHEMA ALTER AUTHORIZATION ON OBJECT

 

如果用户数和角色数超过 32,767,则发生溢出或返回 NULL。

有关详细信息,请参阅查询 SQL Server 系统目录。

info

smallint

标识为仅供参考。不提供支持。不保证以后的兼容性。

status

int

标识为仅供参考。不提供支持。不保证以后的兼容性。

base_schema_ver

int

标识为仅供参考。不提供支持。不保证以后的兼容性。

replinfo

int

标识为仅供参考。不提供支持。不保证以后的兼容性。

parent_obj

int

父对象的对象标识号。例如,表 ID(如果父对象是触发器或约束)。

crdate

datetime

对象的创建日期。

ftcatid

smallint

注册为使用全文索引的所有用户表的全文目录标识符,对于没有注册的所有用户表则为 0。

schema_ver

int

在每次更改表的架构时都会增加的版本号。始终返回 0。

stats_schema_ver

int

标识为仅供参考。不提供支持。不保证以后的兼容性。

type

char(2)

对象类型。可以是下列值之一:

AF = 聚合函数 (CLR)

C = CHECK 约束

D = 默认值或 DEFAULT 约束

F = FOREIGN KEY 约束

FN = 标量函数

FS = 程序集 (CLR) 标量函数

FT = 程序集 (CLR) 表值函数 IF = 内联表函数

IT – 内部表

K = PRIMARY KEY 或 UNIQUE 约束

L = 日志

P = 存储过程

PC = 程序集 (CLR) 存储过程

R = 规则

RF = 复制筛选存储过程

S = 系统表

SN = 同义词

SQ = 服务队列

TA = 程序集 (CLR) DML 触发器

TF = 表函数

TR = SQL DML 触发器

TT = 表类型

U = 用户表

V = 视图

X = 扩展存储过程

userstat

smallint

标识为仅供参考。不提供支持。不保证以后的兼容性。

sysstat

smallint

标识为仅供参考。不提供支持。不保证以后的兼容性。

indexdel

smallint

标识为仅供参考。不提供支持。不保证以后的兼容性。

refdate

datetime

标识为仅供参考。不提供支持。不保证以后的兼容性。

version

int

标识为仅供参考。不提供支持。不保证以后的兼容性。

deltrig

int

标识为仅供参考。不提供支持。不保证以后的兼容性。

instrig

int

标识为仅供参考。不提供支持。不保证以后的兼容性。

updtrig

int

标识为仅供参考。不提供支持。不保证以后的兼容性。

seltrig

int

标识为仅供参考。不提供支持。不保证以后的兼容性。

category

int

用于发布、约束和标识。

cache

smallint

标识为仅供参考。不提供支持。不保证以后的兼容性。