MYSQL select by URL

Question

I have a table looks like this:

id    url                                         title
1     http://afef.com/abc/def/gje/qkd             hello
2     http://afe.com/?a=3&b=2&fse=3               hello
3     http://stackoverflow.com/fefw/sefac/fsg     hello-world

from here id is primary key and auto_increment, and url is unique, but title can be duplicated.

From this point of view, when I add new URL to this table, like :

INSERT IGNORE INTO table (url, title)
VALUES ('http://abcd.com/affefef/sfsaa/?a=3', 'this is title')

Then, if it is new URL to insert, it doesn't matter. But if it is duplicated URL, it will be ignored and I can't know what the duplicated URL's id is.

The id must not be changed.

Is there any solutions to know id when I INSERT duplicated URL with one query ?

Answer 1

Conditionally checking would help in getting duplicate ID

Check this code:

 mysql.query("SELECT id FROM table  WHERE url = 'http://abcd.com/affefef/sfsaa/?a=3", function(error, result, field) {
            if(error) {
                exist(error); //No error
            } else if (result) {
                if (result.length > 0){
                    console.log("Id exist:" + result['id']);
                }else{

                // write your insert statement here
                }
            }
        });

Answer 2

In one query only, I think it's not possible. But you can use the Mysql function "last_insert_id" to retrieve the id which has been inserted. Checking it, you would be able to see if it's a new one or not. See http://www.mysqltutorial.org/mysql-last_insert_id.aspx

You can also have a look at "insert on duplicate". With this syntax it will update the field if it exists or inserts a new one if the key isn't found.

https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html