×
Clear all filters including search bar
Valeri Tandilashvili's MySQL Notes
Results: 175
MySQL system users
Shows system users
SELECT user, authentication_string, plugin, host FROM mysql.user;
disable safe update 
# Disables safe update temporarily 
SET SQL_SAFE_UPDATES = 0;

# Updates multiple rows at once
UPDATE notes SET priority = 100 WHERE priority = 0;

# Enables safe update
SET SQL_SAFE_UPDATES = 1;
INSERT statement
Inserts a single record into students table. In the 
VALUES
clause we list the values in the same order as the columns are defined in the first clause
INSERT INTO `students` (
    `id`, 
    `first_name`, 
    `last_name`, 
    `points`, 
    `mail`, 
    `santa_id`
) 
VALUES (
    NULL, 
    'დავით', 
    'ბერიძე', 
    '54', 
    'david@gmail.com', 
    '10'
);
Inserts several rows with one INSERT statement
INSERT INTO `students` (
    `id`, 
    `first_name`, 
    `last_name`, 
    `points`, 
    `mail`, 
    `santa_id`
) 
VALUES 
	(NULL, 'დავით', 'ბერიძე', '54', 'david@gmail.com', '10'),
	(NULL, 'გელა', 'თავაძე', '54', 'david@gmail.com', '10'),
	(NULL, 'თამარ', 'დავითაშვილი', '54', 'david@gmail.com', '10')
;
We can exclude nullable fields and pass only the necessary fields
INSERT INTO `students` (
    `first_name`, 
    `last_name`, 
    `points`
) 
VALUES (
    'დავით', 
    'ბერიძე', 
    '54'
)
INSERT & SELECT statement
INSERT INTO `students` (`first_name`, `last_name`, `points`) 
SELECT first_name, last_name, points * 1.2 FROM students WHERE id = 3
We can use 
sub-query
in INSERT statement. Before inserting the record, 
sub-query
gets 
gender_id
based on the provided gender name
INSERT INTO students (
    first_name, 
    last_name, 
    points,
    gender_id
) 
VALUES (
    'ილია', 
    'დავითაშვილი', 
    '84',
    (SELECT id FROM genders WHERE name = 'Male')
)
```
issues when upgrading MySQL from 5.5 to 8.0
Row with datetime value '0000-00-00 00:00:00' is not allowed
INSERT INTO `t_cal_mention` VALUES 
(1, 4, 1426, '0000-00-00 00:00:00', 0)
The insert query will produce the following MySQL error
Error Code: 1292. Incorrect datetime value: '0000-00-00 00:00:00' for column inserted_at at row 14
Row with date value '0000-00-00' is not allowed
INSERT INTO `t_design` VALUES (5,0,'წმ. დიდმოწამე მარინე','წმ. დიდმოწამე მარინე','წმ. დიდმოწამე მარინე','oi8io_104355.jpg','0000-00-00','')
The insert query will produce the following MySQL error
Error Code: 1292. Incorrect date value: '0000-00-00' for column 'date' at row 14
enable db connection to another server
View settings:
getsebool -a | grep httpd
Setting to enable to connect to FB to another server:
setsebool -P httpd_can_network_connect_db 1
Export all table except ...
Exports all table from 
dbname
except the two table: 
table1
and 
table2
mysqldump -u root -p --ignore-table=dbname.table1 --ignore-table=dbname.table2 dbname > dbname.sql
Delete binary logs
Deletes binary logs created before the specified date
purge binary logs before ‘2022-03-03’;
LAST_DAY
Function 
LAST_DAY
takes date and returns the last date of the month. In the example the result is 
28
because the year is not leap year
SELECT 
    LAST_DAY('2021-02-15')
DEGREES
Converts radians back to degrees. Half of 
PI
radians 
1.5707963267948966
converted to degrees gives us 
90
degrees. 
PI
: 
3.141592653589793
radians converted to degrees gives us 
180
degrees.
SELECT
    DEGREES(1.5707963267948966),
    DEGREES(3.141592653589793)
tinyint data type
In Laravel 8+ 
tinyInteger
method generates 
tinyint(4)
data type in MySQL
$table->tinyInteger('column_name');
Whereas 
boolean
method generates 
tinyint(1)
data type
$table->$table->boolean('column_name');
Results: 175