Questions and Answers on MySQL and MySql + PHP

How can I find the next AUTO_INCREMENT value from a table in MySQL?

$res=mysql_query("SHOW TABLE STATUS LIKE 'tbl'");
if($row=mysql_fetch_assoc($res))
    return intval($row['Auto_increment']);
else
    return 1;

Pay attention, on the Internet often there is an erroneous way of definition of number (id) of record which will be added:

SELECT max (id) +1 as q FROM tbl

This is not true! Because if the last record was deleted, the id value of the added record will not match MAX + 1!

In addition, with a large number of simultaneous requests to the database from different clients, the id can become more than just received by the function SHOW TABLE STATUS LIKE 'tbl' since could slip through INSERT. For example id: 1,2,3,4 Deleted 3. The following is added 5, not 3.

How to set the initial value for a field with auto increment in MySQL?

To reset the autoincrement field to the initial value of an already existing data table, issue the command:

ALTER TABLE tab SET AUTO_INCREMENT = 1;

After this, when added, the next record will be the number of the largest id + 1

If you need to create a table:

CREATE TABLE tab (id INT UNSIGNED AUTO_INCREMENT, .... PRIMARY KEY (id)) AUTO_INCREMENT = 123;

How to use id free records?

After deleting a record in the database table, the auto-incremental id is freed and will never be used. This is done specifically to ensure the uniqueness of operations. For example, when solving the issue of synchronization in distributed databases.

If you use id for numbering, which you show to the user, the "holes" in the numbering are not very nice.

There are two options:

first:

After each deletion, reset AUTO_INCREMENT to 1 using the command:

alter table `tbl` auto_increment = 1

Example:

mysql_query("DELETE FROM `tbl` WHERE id='".$id."' LIMIT 1");
if(mysql_affected_rows()>0)
    mysql_query("alter table `tbl` auto_increment=1;");

second:

After adding an entry to get its id, use the following instead of the standard function mysql_insert_id ():

/* получить id только что добавленной записи и
   по возможности переместить её в свободный id */
function GetInsertId($tbl){
global $insert_id;
$id0=mysql_insert_id(); if($id0<1) return $id0;
$id_from=(isset($insert_id[$tbl])?$insert_id[$tbl]:1);

for($id=$id_from; $id<$id0; $id++){
   $result = mysql_query('SELECT id from '.$tbl.' WHERE id='.$id.' LIMIT 1');
   if(mysql_num_rows($result)==0){
    mysql_query('UPDATE IGNORE '.$tbl.' SET id='.$id.' WHERE id='.$id0.' LIMIT 1');
    if(mysql_affected_rows()<1)continue; // уже заняли
        $insert_id[$tbl]=$id+1;
    return $id;    // id - свободен
   }
}
$insert_id[$tbl]=$id;
return $id0;
}

It looks for a "hole" in the numbering (free id numbers) and tries to take the first free, if successful, then the last added entry will have the id of the "hole" and the number returned. In case of frequent use, the data is cached into a memory variable.

Output from linked databases

Output all fields from KATEG and the name field of GR. Be careful with the same field names. They are taken from the second base.

$query = mysql_query("SELECT *,gr.name as name FROM kateg,gr WHERE kateg.gr=gr.id ORDER by name");

Checking whether a condition record exists

If there is at least one record satisfying the condition:

if(mysql_num_rows(mysql_query('SELECT * from kateg WHERE gr='.$del.' LIMIT 1'))>0)
    die('Не удаляю, т.к.....!');

Deleting in linked databases

Sometimes I want to write a query like this:

mysql_query("DELETE FROM price,supplier WHERE price.id=supplier.tovar and price.gr=".$del);

But it will not work, here is the equivalent working code:

$result=mysql_query('SELECT * from price WHERE price.gr='.$del);
while ($row = mysql_fetch_assoc($result)){ // удаляю во второй базе множество записей
    mysql_query('DELETE FROM supplier WHERE supplier.tovar='.$row['id']);
}
mysql_query('DELETE FROM price WHERE price.gr='.$del); // удаляю в первой базе

Number of records

Calculate the number of records that satisfy the condition

$q=mysql_query('SELECT count(*) as counter from price WHERE gr='.$row['id']);
$data=mysql_fetch_assoc($q);
$counter=($data?intval($data['counter']):0);

Getting the key of the entry you just added

To get the key of the newly added record, use the function

mysql_insert_id ()

mysql_query("INSERT INTO mytable (product) values ('kossu')");
echo "id последней добавленной записи ".mysql_insert_id();

Renumber the id UNIQUE AUTO_INCREMENT field in mysql

In 99 percent of cases this is not necessary. A unique key is created to ensure that when deleting a record, there is a "hole" in the numbering. But, sometimes it is necessary. For example, I created a site and typed a directory for the client on the local machine, of course, there were a lot of edits. I wanted to "lay out live" to make everything look beautiful. Here is the case for the following script:

$q=sql('SELECT count(*) as counter from gr');
$data=mysql_fetch_assoc($q); $counter=($data?intval($data['counter']):0);
for($id=1; $id<=$counter; $id++){ // перенумеровываю все id
    $result = sql('SELECT * from gr WHERE id='.$id.' LIMIT 1');
    if(mysql_num_rows($result)==0) {// id - свободен
        $result = sql('SELECT MAX(id) as id from gr');
        if (($row = mysql_fetch_assoc($result)) && ($id<$row['id'])){
            sql('UPDATE gr SET id=LAST_INSERT_ID('.$id.') WHERE id='.$row['id'].' LIMIT 1');
            // то же сделать во всех связанных базах!
            sql('UPDATE price SET gr='.$id.' WHERE gr='.$row['id']);
            sql('UPDATE kateg SET gr='.$id.' WHERE gr='.$row['id']);
        }
        else break;
    }
}

Converting the IP address to write to the MySql database

function int2ip($i)
{ // INET_NTOA(3520061480)
$d[0]=(int)($i/256/256/256);
$d[1]=(int)(($i-$d[0]*256*256*256)/256/256);
$d[2]=(int)(($i-$d[0]*256*256*256-$d[1]*256*256)/256);
$d[3]=$i-$d[0]*256*256*256-$d[1]*256*256-$d[2]*256;
return "$d[0].$d[1].$d[2].$d[3]";
}


function ip2int($ip)
{ // INET_ATON("209.207.224.40")
$a=explode(".",$ip);
return $a[0]*256*256*256+$a[1]*256*256+$a[2]*256+$a[3];
}

How to get a list of id records in a string by a condition from MySql

To select a list of records in the form of a line of id codes, use the MySql function GROUP_CONCAT

SELECT GROUP_CONCAT(DISTINCT id SEPARATOR ',') AS plan_id
    FROM table
    WHERE `Group` = 1

Find lost records in linked tables 
(entries that do not matter in the directory)

SELECT * FROM tovar
    LEFT JOIN category_link
        ON tovar.id=category_link.tovar
    WHERE category_link.tovar IS NULL

If you need to find and delete lost records:

SELECT swdist_head.dist_id
    FROM swdist_head LEFT JOIN swdist_item
    ON swdist_head.dist_id = swdist_item.dist_id
    WHERE swdist_item.dist_id IS NULL;

The corresponding DELETE clause will be:

DELETE swdist_head
    FROM swdist_head LEFT JOIN swdist_item
    ON swdist_head.dist_id = swdist_item.dist_id
    WHERE swdist_item.dist_id IS NULL;

The query that defines descendants without parents looks like this:

SELECT swdist_item.dist_id
    FROM swdist_item LEFT JOIN swdist_head
    ON swdist_item.dist_id = swdist_head.dist_id
    WHERE swdist_head.dist_id IS NULL;

The corresponding DELETE clause removes them:

DELETE swdist_item
    FROM swdist_item LEFT JOIN swdist_head
    ON swdist_item.dist_id = swdist_head.dist_id
    WHERE swdist_head.dist_id IS NULL;

How many messages were sent during each hour of the day?

SELECT HOUR(t) AS hour, COUNT(HOUR(t)) AS count FROM mail GROUP BY hour;

Converting TIMESTAMP to DATETIME and back

SELECT DATE_FORMAT(t_create,'%Y-%m-%d %T') FROM tbl; // CCYYMMDDhhmmss -> CCYY-MM-DD hh:mm:ss

SELECT DATE_FORMAT(dt,'%Y%m%d%H%i%s'), dt+0 FROM tbl;  // CCYY-MM-DD hh:mm:ss -> CCYYMMDDhhmmss

MyISAM or InnoDB?

MyISAM is faster than InnoDB - doubtful. They are designed for different types of applications:

• MyISAM - tablets designed for applications with a very large margin in the direction of reading (for example, they will behave well at 1000 readings per 1 record). It should be understood that they do not support simultaneous recording. Those. At the time of inserting a record in the table, all other requests are hanging and waiting!
• InnoDB - supports simultaneous recording with very low performance degradation reads compared to MyISAM. Actually, in the presence of write requests, parallel readings from the same InnoDB are possible (in contrast to MyISAM), and, accordingly, the performance of such tables will be on average higher.

Mysql: how to ignore errors when loading a dump?

Use the "-f" switch to continue, even if an error occurred during the process. For example:

mysql.exe --default-character-set=cp1251 -f -u root -p --database=base1 < !.!

Long MySQL query and what to replace it with?

This query on a table of 100 thousand records takes more than 10 seconds

SELECT user_id FROM `user_spy` WHERE key in (
    SELECT key FROM `user_spy` WHERE `user_id` = 32602)
AND user_id != 32602

If it is divided into two, then each will be executed in less than a second:

SELECT group_concat(distinct key) FROM `user_spy` WHERE `user_id` = 32602
+
SELECT user_id FROM user_spy WHERE key in ('xxxx','yyy') AND user_id != 32602

mysql change the encoding of the base and all fields

Request for database conversion:

ALTER TABLE `db_name`.`table_name` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

If you have a MySQL server version 5+ you're lucky! who can execute this request:

SELECT CONCAT('ALTER TABLE `', t.`TABLE_SCHEMA`, '`.`', t.`TABLE_NAME`, '` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') as sqlcode
FROM `information_schema`.`TABLES` t
WHERE 1
AND t.`TABLE_SCHEMA` = 'db_name'
ORDER BY 1

Replace it! db_name to the name of your Database.

After the query is executed, we get the SQL response in the style of:

ALTER TABLE `bla_bla`.`bla_brands` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `bla_bla`.`bla_models` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `bla_bla`.`bla_modify` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `bla_bla`.`bla_params` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `bla_bla`.`bla_param_groups` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `bla_bla`.`bla_param_names` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `bla_bla`.`bla_submodels` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `bla_bla`.`province` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `bla_bla`.`users` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

We copy it, paste it into the SQL query form and execute, the result is the completely changed encoding of all tables on utf8 - we enjoy the saved time!

And if you need utf8_general_ci tables to convert to utf8mb4_general_ci in order for emoji emoticons to be saved:

ALTER TABLE xxx MODIFY COLUMN yyy COLLATE utf8mb4_general_ci, ... ;

In addition to that the database should be with the correct encoding, the connection should also be installed with the correct encoding:

mysqli_query("set names 'utf8'");