Category: Database

MySQL: How to Copy tables into seperate Schemas

To Copy a single table in a Schema into another existing Schema

CREATE TABLE myschema.mytbl LIKE myschema2.mytbl;
INSERT INTO myschema.mytbl SELECT * FROM myschema2.mytbl;

To Copy all the tables in a Schema into another existing Schema

 
In cmd/terminal, dump the database

mysqldump -u root -ppass myschema > /path/to/myschema.sql

In cmd/terminal, use the new database and import

mysql -u root -ppass

USE newschema;

source /path/to/myschema.sql

Create a New MySQL User that only has access to a Particular Schema and Table

Create New MySQl User with Privileges

GRANT ALL PRIVILEGES ON dbTest.* To 'user'@'hostname' IDENTIFIED BY 'password';

So for example:

Log in to Mysql…

mysql -u [root] -p

Then type:

GRANT ALL PRIVILEGES ON [schema].[table “” not found /]
To '[user]'@'[hostname]' IDENTIFIED BY '[password]';

How to View Current Users and Privileges

SHOW GRANTS;

or

select user,host from mysql.user;

MySQL INSERT IF NOT EXISTS: Checking whether a record exists and inserting

MySQL Insert If Not Exists

Why doesn’t MySQL Insert If Not exists work?????

UPDATE: CHECK AT THE BOTTOM OF POST

Bogdan has the answers .

First of all, this information relates solely to MySQL and may not apply to MSSQL or PostgreSQL.

Have you been trying to insert records into a table based on whether there is already a record there of not. Trying multiple SQL queries of the form:

INSERT INTO list
(code, name, place)
SELECT *
FROM
(SELECT ('ABC', 'abc ', 'Johannesburg',)) AS tmp
WHERE NOT EXISTS 
(
SELECT * FROM sharelist WHERE code = 'ABC'
);

Some people also use:

INSERT INTO table (fields) VALUES (values) ON DUPLICATE KEY UPDATE ID=ID;

Provided you have a unique primary key. This is not the correct way of using a Mysql Insert if not Exists…

Well Bogdan has the answers.

There is syntax used to tackle this exact problem in an efficient way and that is:


INSERT IGNORE INTO `list`
`code` = 'ABC',
`name` = 'abc',
`place` = 'Johannesburg',
;

It’s that easy.

If the record exists it will be ignored (silently skipped). If it does not it will be inserted.

Warm and Easy.

Update: No maybe not so easy. Damn you Mysql Insert if not Exists

The problem arises when you have a field (most likely an id) which is not added by you. So an auto incremented field. The above code will not skip the insert and will always insert the record in as it is unique because of the auto increment field.

So if you have an auto increment use the following:


INSERT INTO list (code, name, address,)
SELECT 'ABC', 'ABC name', 'Johannesburg'
FROM list
WHERE NOT EXISTS(
    SELECT code, name, address
    FROM list
    WHERE code = 'ABC'
	AND name = 'ABC name'
	AND address = 'Johannesburg'
)
LIMIT 1

That will do the job. However, one important point is that the table cannot be empty it must have at least one field so that is unfortunately a flaw.

Who thought a Mysql insert if not Exists would be this (easy) hard.

Read this question on stack: prevent autoincrement on MYSQL duplicate insert

Original article found on this blog: Mr.TimRosenblatt

If someone has a better idea please contact me: ME or add a comment.

Thanks
MySQL insert if not exists