The news database table of cases

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Netkiller MySQL.


MySQL MariaDB...


Mr. Neo Chan,Chen Jingfeng(BG7NYT)


Guangdong Province Shenzhen city Longhua new city streets China mountain beauty
518131
+86 13113668890
+86 755 29812080
<>





The document was founded in 2010-11-18



Copyright statement

Reprint please contact the author, please indicate the original source of the article and the author information and this statement when reprinting.

Document source:
http://netkiller.github.io
http://netkiller.sourceforge.net

 



$Date: 2013-04-10 15:03:49 +0800 (Wed, 10 Apr 2013) $


A series of documents on my

 

Netkiller Architect. Netkiller Developer. Netkiller PHP. Netkiller Python. Netkiller Testing. Netkiller Cryptography.
Netkiller Linux. Netkiller CentOS. Netkiller FreeBSD. Netkiller Security. Netkiller Version. Netkiller Web.
Netkiller Monitoring. Netkiller Storage. Netkiller Mail. Netkiller Shell. Netkiller Network. Netkiller Database.
Netkiller PostgreSQL. Netkiller MySQL. Netkiller NoSQL. Netkiller LDAP. The Netkiller Cisco IOS. Netkiller H3C.
Netkiller Multimedia. Netkiller Docbook. Netkiller open source software.      

 





 

 


 

4.16.3. news database table of cases


Here I by a news website as an example, to solve the problem of table

Avoid development often merge table, I used a put things right once and for all, to establish a news table using the black hole engine, then starting device data will be diverted to the matching table. At the same time using UUID instead of a sequence of numbers, can guarantee that it will not in future years as ID.

		
CREATE TABLE IF NOT EXISTS `news` (
  `uuid` varchar(36) NOT NULL COMMENT 'Only ID',
  `title` varchar(50) NOT NULL COMMENT 'News headlines',
  `body` text NOT NULL COMMENT 'The news text',
  `ctime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Create time',
  `mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Modification time',
  `atime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Access time',
  PRIMARY KEY (`uuid`)
) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8;
		
		

The table is used only for example, relatively simple structure. Create table next year, you can also each month a table, flexible adjustment according to your promise. The table structure and above news table the same, pay attention to ENGINE=InnoDB.

		
CREATE TABLE IF NOT EXISTS `news_2012` (
  `uuid` varchar(36) NOT NULL COMMENT 'Only ID',
  `title` varchar(50) NOT NULL COMMENT 'News headlines',
  `body` text NOT NULL COMMENT 'The news text',
  `ctime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Create time',
  `mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Modification time',
  `atime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Access time',
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='The news table';

CREATE TABLE IF NOT EXISTS `news_2013` (
  `uuid` varchar(36) NOT NULL COMMENT 'Only ID',
  `title` varchar(50) NOT NULL COMMENT 'News headlines',
  `body` text NOT NULL COMMENT 'The news text',
  `ctime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Create time',
  `mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Modification time',
  `atime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Access time',
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='The news table';
		
		

The UUID index table, the main function is through the UUID query the records in the table. A better solution is to data in the Solr treatment, including the title and content search etc.

		
CREATE TABLE `news_index` (
	`uuid` VARCHAR(36) NOT NULL,
	`tbl_name` VARCHAR(10) NOT NULL,
	PRIMARY KEY (`uuid`)
)
COMMENT='The news UUID index table'
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

		
		

The news_insert process, is used to insert data into the destination table, can separate call but is not recommended. Because insert is much more general than call, to consider the portability and versatility

		
DELIMITER //
CREATE DEFINER=`neo`@`%` PROCEDURE `news_insert`(IN `uuid` vARCHAR(36), IN `title` VARCHAR(50), IN `body` TEXT, IN `ctime` TIMESTAMP)
BEGIN
	if year(ctime) = '2012' then
		insert into news_2012(uuid,title,body,ctime) values(uuid,title, body, ctime);
	end if;
	if year(ctime) = '2013' then
		insert into news_2013(uuid,title,body,ctime) values(uuid,title, body, ctime);
	end if;
	insert into news_index values(uuid, year(ctime));
END//
DELIMITER ;
		
		

Insert triggers, responsible for getting UUID and then call a stored procedure

		
SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='';
DELIMITER //
CREATE TRIGGER `news_before_insert` BEFORE INSERT ON `news` FOR EACH ROW BEGIN
	IF new.uuid is null or new.uuid = '' or length(new.uuid) != 36 THEN
		set new.uuid=uuid();
	END IF;
	call news_insert(new.uuid,new.title,new.body,new.ctime);
END//
DELIMITER ;
SET SQL_MODE=@OLDTMP_SQL_MODE;
		
		

This trigger users to protect the UUID value in the table are not modified.

		
SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='';
DELIMITER //
CREATE TRIGGER `news_before_update` BEFORE UPDATE ON `news_2013` FOR EACH ROW BEGIN
	set new.uuid = old.uuid;
END//
DELIMITER ;
SET SQL_MODE=@OLDTMP_SQL_MODE;

 

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Meredith at November 28, 2013 - 9:46 AM