Mysql trigger for checking duplicate record in Table before insert using trigger

I am to creating a trigger for my table User which checks for duplicates (Mobile number) in the User table before inserting a new row. Assume we do not have any unique index added hence we have to manage uniqueness by programatically.

The insert trigger executes prior to the insertion process. Only if the code succeeds, will the insert take place. To prevent the row from being inserted, an error must be generated. Below is the trigger

DROP TRIGGER if EXISTS before_user_insert; DELIMITER $$ CREATE TRIGGER before_user_insert BEFORE INSERT ON users FOR EACH ROW BEGIN DECLARE v1 BIGINT(20); DECLARE m1 VARCHAR(400); SELECT id INTO v1 FROM users WHERE id<>NEW.id AND `mobile`=NEW.`mobile`; IF (v1 IS NOT NULL) THEN SELECT CONCAT('Duplicate mobile number at record - ', v1) INTO m1; SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT = m1; END IF; END$$ DELIMITER ;

Which will output as below: SQLSTATE[45000]: <>: 1644 Duplicate mobile number at record - 43

How to fix the MySQL error: Illegal mix of collations for operation union

Well, you probably have different collations in some MySQL views or stored procedures, try to force collation like this:

SET character_set_client = 'utf8mb4'; SET character_set_connection = 'utf8mb4'; SET collation_connection = 'utf8mb4_unicode_ci'; drop view if exists myviewwithproblem; create view myviewwithproblem as … da da etc etc

Another solution might be:

I ran into this recently as well. In my case the relevant columns were utf8mb4_unicode_ci, but I found out that the session was utf8mb4_general_ci. You can see this from the collation_connection variable: CODE: SELECT ALL SHOW VARIABLES LIKE '%collat%'; I found that this came from the connection string including "Character Set=utf8mb4". This caused a "SET NAMES UTF8MB4;" query to be run on every connection, which causes MySQL to take the default collation for this character set, which is utf8mb4_general_ci. Updating the server's character set and collation to match the data (setting character-set-server and collation-server in the server config) and then using "Character Set=Auto" caused the connection to have the correct collation, and the issue was fixed. Manually running something like "SET NAMES UTF8MB4 COLLATE utf8mb4_unicode_ci" should fix it, too.

How to export some rows of a MySQL table using WHERE clause - Selectively dumping data with mysqldump feature - MySQL export few rows from selected table as SQL file

How to export some rows of a MySQL table with where clause? I have a MySQL say test and I want to create a importable .sql file for rows where id are between 10 and 100. The way is to use mysqldump feature of MySQL server.

This is the command which will export few rows (for my case where id>=1 and id<=10) into a file: c:\xampp\mysql\bin\mysqldump.exe -uroot DatabaseName TableName --where="id>=1 and id<=10" > c:\Users\HP\Downloads\Export.sql

You can also use the –where flag to selectively dump data from more than one table, but obviously the columns specified in the where clause need to be in both tables LIKE c:\xampp\mysql\bin\mysqldump.exe -uroot DatabaseName TableName1 TableName2 --where="id>=1 and id<=10" > c:\Users\HP\Downloads\Export.sql

Where can I find the MySQL log file in XAMPP - Activate logs in Xampp-Mysql

I use PHP (PDO) to access MySQL in XAMPP. My question is where I can find the MySQL query log, exact query that executed on MySQL server?

You need to run these two queries: SET GLOBAL general_log = 'ON'; SET GLOBAL general_log_file = 'my_log.log';

First will enable loging (which may be off by default) and the second select updates the preferred file (by default under C:/xampp/mysql/data/)

NOTE: On windows 8 you may have to run your SQL IDE as ADMINISTRATOR for this commands to get saved.

You can also set this in the config, go to path_to_xampp/mysql/ and edit my.ini (copy from my-default.ini if it does not exists) and add the settings there:

[mysqld] general_log = 'ON'; general_log_file = 'my_log.log'; sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

You can view your query logs like below window: Step1: You need to install Git Bash Step2: Open Git Bash terminal and navigate to xampp_install_directory/mysql/data directory Step3: Run command tail -f my_log.log

PHP & MySQL PDO: PDO binding values for MySQL IN statement - Bind an array to an IN() condition?

The answer there was that, for a variable sized list in the in clause, you'll need to construct the query yourself.

Consider your array like:

$values = array(1, 2, 3, 4, 5); $count = count($values); $criteria = sprintf("?%s", str_repeat(",?", ($count ? $count - 1 : 0))); $sql = sprintf("DELETE FROM table where column NOT IN(%s)", $criteria); $pdo->sth = prepare($sql); $pdo->sth->execute($values); SQL should be: DELETE FROM table where column IN (?,?,?,?,?)

Rollback or Commit with PDO transaction using PHP and MySQL - SELECT query with PDO - PDO with INSERT INTO through prepared statements

A transaction should end with either a rollback() or a commit(), (only one of them). In case you are using MySQL, make sure you are not using MyISAM engine for tables, as it doesn't support transactions. It usually support InnoDB. Some databases, including MySQL, automatically issue an implicit COMMIT when a database definition language (DDL) statement such as DROP TABLE or CREATE TABLE is issued within a transaction. The implicit COMMIT will prevent you from rolling back any other changes within the transaction boundary.

<?php class DB { public static function transactional($closure) { $dbh = null; try { $dbh = new PDO("mysql:host=HOST_NAME;dbname=DB_NAME", "DB_USER_NAME", "DB_USER_PASSWORD"); /*** Set the PDO error mode to exception (will throw exception if any error occurred) ***/ /*** Follow the link for more info: http://php.net/manual/en/pdo.setattribute.php ***/ $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $dbh->setAttribute(PDO::ATTR_ORACLE_NULLS, PDO::NULL_EMPTY_STRING); /*** Transaction block starting here ***/ $dbh->beginTransaction(); $closure($dbh); $dbh->commit(); } catch(Throwable $ex) { $dbh->rollback(); throw $ex; } } } DB::transactional(function (PDO $PDO) { $PDO->prepare("INSERT INTO records (`RegistrationID`) VALUES(:RegistrationID)")->execute(['RegistrationID' => 'RegistrationID 1']); $PDO->prepare("INSERT INTO records (`RegistrationID`) VALUES(:RegistrationID)")->execute(['RegistrationID' => 'RegistrationID 2']); // $PDO->query used to get results $result = $PDO->prepare("SELECT * FROM records WHERE `RegistrationID` LIKE :RegistrationID"); $result->execute(['RegistrationID' => '%RegistrationID%']); echo "<pre>"; print_r($result->fetchAll()); echo "</pre>"; Below line will throw an exception and data will not persist into database. To persist data into database, comment below line. $x = null + null * $PDO->dd(); });

Output be as follows: Array ( [0] => Array ( [id] => 12 [0] => 12 [RegistrationID] => RegistrationID 1 [1] => RegistrationID 1 ) [1] => Array ( [id] => 13 [0] => 13 [RegistrationID] => RegistrationID 2 [1] => RegistrationID 2 ) )

MySQL "Group By" and "Order By"

A simple solution is to wrap the query into a subselect with the ORDER statement first and applying the GROUP BY later:

SELECT * FROM ( SELECT `timestamp`, `fromEmail`, `subject` FROM `incomingEmails` ORDER BY `timestamp` DESC ) AS tmp_table GROUP BY LOWER(`fromEmail`)

This is similar to using the join but looks much nicer. Using non-aggregate columns in a SELECT with a GROUP BY clause is non-standard. MySQL will generally return the values of the first row it finds and discard the rest. Any ORDER BY clauses will only apply to the returned column value, not to the discarded ones. IMPORTANT UPDATE Selecting non-aggregate columns used to work in practice but should not be relied upon. Per the MySQL documentation "this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate."

How to convert result table to JSON Array or JSON Object or JSON String in MySQL

I'd like to convert result table to JSON Array of Object in MySQL using preferably only plain MySQL commands. For example with query SELECT name, phone FROM person; | name | phone | | Jack | 12345 | | John | 23455 | the expected JSON output would be as below as per my requirement: [ { "name": "Jack", "phone": 12345 }, { "name": "John", "phone": 23455 } ]

The maximum value for group_concat_max_len is 18446744073709551615. To set the variable forever use SET GLOBAL group_concat_max_len=4294967295;

Example 1: SELECT i.id, JSON_OBJECT('id', ig.id, "name", ig.name) AS 'group_json' FROM item i LEFT JOIN item_group ig ON i.item_group_id=ig.id ORDER BY i.id DESC LIMIT 10;

Example 2 (Group Concat): SELECT ig.item_sales_rate_id, GROUP_CONCAT(DISTICT JSON_OBJECT('id', ig.id, "status", ig.status)) AS 'group_json' FROM item_sales_rate i LEFT JOIN item_sales_uom ig ON i.id=ig.item_sales_rate_id WHERE ig.item_sales_rate_id IS NOT NULL GROUP BY ig.item_sales_rate_id ORDER BY i.id ASC LIMIT 10;

MySQL casting from decimal to string - mysql cast to varchar - mysql convert decimal to string char varchar

MySQL - casting from decimal to string select CAST(ROUND(345345345345345345345353453453453454.4345345,6) AS CHAR(65)) AS 'big_number'; don't try to cast to varchar, there is a bug at MySQL end