Chevrons Code

PHP, ASP.net, HTML, JavaScript, Yii and MySQL

The way to insert Chinese character into MySQL table. You need set the table structure charset to utf8.

CREATE TABLE IF NOT EXISTS `contents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

After that, add the following code as below:

mysql_connect(HOST, USER, PASSWORD);
mysql_select_db(DBNAME);
mysql_query("SET NAMES 'utf8'");
mysql_query("SET CHARACTER_SET_CLIENT=utf8");
mysql_query("SET CHARACTER_SET_RESULTS=utf8");

Tables or result sets sometimes contain duplicate records. Sometimes, it is allowed but sometimes it is required to stop duplicate records. Sometimes, it is required to identify duplicate records and remove them from the table. This chapter will describe how to prevent duplicate records occurring in a table and how to remove already existing duplicate records.

You are allow use PRIMARY KEY or UNIQUE Index on a table with appropriate fields to stop duplicate records. Let’s create an example table with id and name as Primary Key:

CREATE TABLE `foo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `value` int(11) NOT NULL,
  PRIMARY KEY (`id`, `name`),
);

There may be times when a specific order is required in a SQL query which cannot be done using either ASC or DESC or using a special sort field. MySQL has a ORDER BY FIELD function which can be used to do this.

Example Data
The example data in this post uses as below. This is a somewhat simple table but it can be used to illustrate the point in this post quite well.

CREATE TABLE fruits (`id` int, `name` varchar(50));
	
INSERT INTO fruits (`id`, `name`)
VALUES (1, 'Apple'),
       (2, 'Durian'),
       (3, 'Banana'),
       (4, 'Lemon'),
       (5, 'Pear'),
       (6, 'Star fruit'),
       (7, 'Strawberry'),
       (8, 'Orange');

In MySQL Workbench alter table there have 7 column flags available: PK, NN, UQ, BIN, UN, ZF and AI.

Because sometimes need to know last day of the previous month, and I was using procedure/routine to completed the query. That’s the reason I can’t use others programming language to do it. All the thing must finish in MySQL server only.

And very lucky, MySQL have “last_day()” function to get last day of the month.

Below example are showing how to use it.

Recently I reinstall MySQL Database because it suddenly cannot start and it crash. After I successful reinstall, I found an extra database in MySQL Server.

When I tried to drop the database, I received the following error:

“Can’t drop database ‘dbname’; database doesn’t exist”

And when I tried to create a new database with same name, it told me:

“Can’t create database ‘dbname’; database exists”

That just didn’t make any sense! And the PHPMyAdmin application still listed the database in the Catalog list.

Sometimes we keep all the records/logs in one table. But if we want retrieve each group last records in one SQL it look like impossible.

Below Example showed how to:

SELECT rs1.*
FROM TABLE rs1 LEFT JOIN TABLE rs2
  ON (rs1.name = rs2.name AND rs1.id < rs2.id)
WHERE rs2.id IS NULL;

Above method can get the last record results group by name.

If you want retrieve first records in each group, just change to

Duplicate row found and then update the row by one sql statement. This can be done by SQL, and not need any others server-side scripting.

Now let us see how it work. First, create table and insert some example data.

CREATE TABLE IF NOT EXISTS `foo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
);

INSERT INTO `foo` (`id`, `name`) VALUES
(1, 'foo'),
(2, 'foo2');

The easiest way to generate random records in Mysql is to use the ORDER BY RAND().

SELECT col1 FROM tbl ORDER BY RAND() LIMIT 1;

This can work fine for small tables. However, for big table, it will have a serious performance problem as in order to generate the list of random rows, because Mysql need to assign random number to each row and then sort them.

Even if you only want random 1 records from 200k+ rows, Mysql need to sort all the records and then extract it.

Because I found that record save inside the database have typo problem.

How to solve it? Normally I will use PHP str_replace to solve the issue. But now requires use SQL to print out all the record and replace the typo.