细节有关问题：ZEROFILL的用法范围

细节问题：ZEROFILL的用法范围。

1、zerofill把月份中的一位数字比如1，2，3等加前导0

mysql>?CREATE?TABLE?t1?(year?YEAR(4),?month?INT(2)?UNSIGNED?ZEROFILL,
????->?day?INT(2)?UNSIGNED?ZEROFILL);
Query OK,?0 rows affected?(0.11 sec)

mysql>?INSERT?INTO?t1?VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2)
????->?(2000,2,23),(2000,2,23);
Query OK,?6 rows affected?(0.03 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql>?select?*?from?t1;
+------+-------+------+
|?year?|?month?|?day?|
+------+-------+------+
|?2000?|?01?|?01?|
|?2000?|?01?|?20?|
|?2000?|?01?|?30?|
|?2000?|?02?|?02?|
|?2000?|?02?|?23?|
|?2000?|?02?|?23?|
+------+-------+------+
6 rows?in?set?(0.02 sec)
2、如果有这样的需求：一个字段宽度为6个字符，不足的补零，而且又要自动增加。MYSQL现在好像还没有提供这样的功能，这里我用存储过程来实现。

创建表：
Table?? Create Table???????????????????????????????????????????????????????????????????????????
------? ---------------------------------------------------
lk14??? CREATE TABLE `lk14` (??????????????????????????????????????????????????????????????????
????????? `id` int(6) unsigned zerofill NOT NULL DEFAULT '000000',?????????????????????????????
????????? `str` char(40) DEFAULT NULL,?????????????????????????????????????????????????????????
????????? PRIMARY KEY (`id`)???????????????????????????????????????????????????????????????????
??????? ) ENGINE=MyISAM DEFAULT CHARSET=gb2312 ROW_FORMAT=DYNAMIC COMMENT='InnoDB free: 0 kB'??


创建SP:

DELIMITER $$

DROP?PROCEDURE IF EXISTS?`test`.`sp_zerofill`$$

CREATE?PROCEDURE?`test`.`sp_zerofill`(IN?num?int)
BEGIN
??declare i?int?default?1;
??-- initialization value
??set?@sqltext?=?'insert into lk14 values(0,''char0'')';
??-- begin while
??while i?<?num do
????set?@sqltext?=?concat(@sqltext,',','(',i,',''char',ceil(num*rand()),''')');
????set?i?=?i?+?1;
??end?while;
??-- begin dynamic sql
??prepare s1?from?@sqltext;
??execute s1;
??deallocate prepare s1;
END$$

DELIMITER?;
调用结果：

call sp_zerofill(6);

select?*?from?lk14 order by id;


query result(6 records)idstr000000char0000001char2000002char2000003char2000004char3000005char5

本文出自 “上帝，咱们不见不散！” 博客，转载请与作者联系！

查看更多 下一篇