MySQL
mysql 5.7.16 installer download url for once click installation
https://cdn.mysql.com/archives/mysql-installer/mysql-installer-community-5.7.16.0.msi
sudo mysql -h host_name_value -P port_num_value -uuser_name_value -p'password_value' db_name;
sudo mysql -h localhost -P 3306 -usam -p'Sam0001!' db;
sudo -s;
vim /etc/mysql/mysql.conf.d/mysqld.cnf
mysql -h localhost -P 3306 -usam -p'Sam0001!' db;
mysql -h servername -P portnum_value -uUsername_value -p'password_value' db_name;
sudo mysql -u;
show databases;
use sys;
show tables;
CREATE USER 'sam'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'sam'@'localhost';
//Got a packet bigger than 'max_allowed_packet' bytes
sudo vim /etc/mysql/mysql.conf.d
//select mysqld.cnf
mysqld.cnf
//open mysqld.cnf
//uncomment max_allowed_packet and modify its value to 1000M as below.
max_allowed_packet = 1000M
//esc+:++w+q+! to save
esc:wq!
//restart mysql
sudo mysql service restart;
create user 'sam'@'localhost' identified by 'password_value';
grant all privileges on *.* to 'sam'@'localhost' with grant option; show grants for 'sam'@'localhost'; ysql> show grants for 'sam'@'localhost'\G; *************************** 1. row *************************** Grants for sam@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `sam`@`localhost` WITH GRANT OPTION *************************** 2. row *************************** Grants for sam@localhost: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO `sam`@`localhost` WITH GRANT OPTION 2 rows in set (0.01 sec)
//empty bin log flush binary logs; purge binary logs before now(); show binary logs;
//disable bin log sudo vim /etc/mysql/mysql.conf.d //select mysqld.cnf mysqld.cnf //open mysqld.cnf //append below to the end of the file mysqld.cnf skip-log-bin = true //esc+:++w+q+!
esc:wq!
//restart mysql
sudo mysql service restart;
mysql> show binary logs;
ERROR 1381 (HY000): You are not using binary logging
sudo: "cd" is a shell built-in command, it cannot be run directly.
sudo: the -s option may be used to run a privileged shell.
sudo: the -D option may be used to run a command in a specific directory.
//Instead try using sudo -s to start a root shell and then simply cd into the directory.When you're done as root, press CtrlD or type exit. sudo -s cd mysql ls -a -lct fred@fred:/var/lib$ sudo -s root@fred:/var/lib# cd mysql root@fred:/var/lib/mysql# ls -a -lct; total 123344 drwx------ 8 mysql mysql 4096 Sep 20 20:52 . -rw-r----- 1 mysql mysql 196608 Sep 20 20:35 '#ib_16384_0.dblwr' -rw-r----- 1 mysql mysql 33554432 Sep 20 20:35 undo_001 -rw-r----- 1 mysql mysql 33554432 Sep 20 20:35 undo_002 -rw-r----- 1 mysql mysql 25165824 Sep 20 20:34 mysql.ibd -rw-r----- 1 mysql mysql 12582912 Sep 20 20:34 ibdata1 drwxr-x--- 2 mysql mysql 4096 Sep 20 20:34 db
linux mysql default directory
mysql> select @@datadir;
+-----------------+
| @@datadir |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)
mysql>
cd /var/lib/mysql/
//and cd to respectively database name,such as db
cd db;
ls -a -lct -lh;
root@fred:/var/lib/mysql/db# ls -a -lct -lh;
total 866G
drwx------ 8 mysql mysql 4.0K Sep 20 20:52 ..
drwxr-x--- 2 mysql mysql 4.0K Sep 20 20:34 .
-rw-r----- 1 mysql mysql 866G Sep 20 20:34 t1.ibd
root@fred:/var/lib/mysql/db#
show mysql columns of table
show columns from database_name.table_name show columns from db.t1; ysql> show columns from db.t1; +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | id | bigint | NO | PRI | NULL | auto_increment | | author | varchar(40) | NO | | NULL | | | comment | varchar(40) | NO | | NULL | | | content | varchar(40) | NO | | NULL | | | header | varchar(40) | NO | | NULL | | | isbn | varchar(40) | NO | | NULL | | | memory | varchar(40) | NO | | NULL | | | object | varchar(40) | NO | | NULL | | | result | varchar(40) | NO | | NULL | | | summary | varchar(40) | NO | | NULL | | | title | varchar(40) | NO | | NULL | | | topic | varchar(40) | NO | | NULL | | +---------+-------------+------+-----+---------+----------------+ 12 rows in set (0.00 sec)
mysql> show create table t1; +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `author` varchar(40) NOT NULL DEFAULT '', `comment` varchar(40) NOT NULL DEFAULT '', `content` varchar(40) NOT NULL DEFAULT '', `header` varchar(40) NOT NULL DEFAULT '', `isbn` varchar(40) NOT NULL DEFAULT '', `memory` varchar(40) NOT NULL DEFAULT '', `object` varchar(40) NOT NULL DEFAULT '', `result` varchar(40) NOT NULL DEFAULT '', `summary` varchar(40) NOT NULL DEFAULT '', `title` varchar(40) NOT NULL DEFAULT '', `topic` varchar(40) NOT NULL DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
ALTER TABLE table_name MODIFY column_name new_data_type;
https://stackoverflow.com/questions/21911733/error-1115-42000-unknown-character-set-utf8mb4
drop database if exists mydb; CREATE DATABASE if not exists mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
use mydb;
drop table if exists t1;
create table if not exists t1
(id bigint unsigned not null auto_increment primary key comment 'pk id',
author varchar(40) not null default '' comment 'book author/creator',
header varchar(40) not null default '' comment 'book header',
isbn varchar(40) not null default '' comment 'book isbn',
summary varchar(40) not null default '' comment 'book summary',
title varchar(40) not null default '' comment 'book title',
topic varchar(40) not null default '' comment 'book topic',
create_time datetime not null default current_timestamp comment 'publish time',
update_time datetime not null default current_timestamp on update current_timestamp comment 'new version publish time')
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
UTF8 is a popular character set that supports a wide range of languages and symbols.
In MySQL, UTF8 can encode up to 3 bytes per character. The maximum character in Unicode however, requires 4 bytes to encode. As a result, UTF8 cannot fully support all Unicode characters.
On the other hand, UTF8MB4 is an extension of UTF8 and uses a maximum of 4 bytes per character. It can fully support all Unicode characters, including emojis and other special characters.
In conclusion, UTF8MB4 prevents data truncation and encoding errors. It also makes it easier to handle data from different languages.
//show mysql view dependent table mysql> show create view information_schema.character_sets; +----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | View | Create View | character_set_client | collation_connection | +----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | CHARACTER_SETS | CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `information_schema`.`CHARACTER_SETS` AS select `cs`.`name` AS `CHARACTER_SET_NAME`,`col`.`name` AS `DEFAULT_COLLATE_NAME`,`cs`.`comment` AS `DESCRIPTION`,`cs`.`mb_max_length` AS `MAXLEN` from (`mysql`.`character_sets` `cs` join `mysql`.`collations` `col` on((`cs`.`default_collation_id` = `col`.`id`))) | utf8mb3 | utf8mb3_general_ci | +----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ 1 row in set (0.00 sec)
mysql> select * from engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | ENGINE | SUPPORT | COMMENT | TRANSACTIONS | XA | SAVEPOINTS | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec)
mysql> select uuid(),md5(uuid()),sha(uuid()),sha1(uuid()),sha2(uuid(),224),sha2(uuid(),256),sha2(uuid(),384),sha2(uuid(),512)\G;
*************************** 1. row ***************************
uuid(): dab9a8d8-8f11-11ee-acec-080027b9fde5
md5(uuid()): 95e323d64b2b19b64bc2b766bc2a4536
sha(uuid()): f54d23449594ca8443f5ad0472fefe35d6b8b378
sha1(uuid()): 895901608fa79d37d316fd2ea0b47135c3300ac6
sha2(uuid(),224): c8caccb5a75ec116cf726983cb1be5e45489d0c7338cecda44397447
sha2(uuid(),256): 3d4e27023aa444e3bf4a31a39ef7603aa087dfee555d31df96d4e791eb372924
sha2(uuid(),384): 0683cf04d6f3983fcba7f67aece3f8971430e92260c48c8475918678ba14335023e1af8d60a72f06e149b08d13916031
sha2(uuid(),512): 288cde959d5e319cc2a038e7dfb3768ea514d6e40770e0049413931588d90d3aa2ea871a7bcc48fe9f381bad4c53c90606f4e8cc4578c379ce560a31ad0614df
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
mysql> select uuid(),md5(uuid()),sha(uuid()),sha1(uuid()),sha2(uuid(),224),sha2(uuid(),256),sha2(uuid(),384),sha2(uuid(),512)\G; *************************** 1. row *************************** uuid(): 6a16ea5e-8f13-11ee-acec-080027b9fde5 md5(uuid()): a36fda3ad332396e55e19ed9d2bf73fd sha(uuid()): 8ee564b364108155a3aa82993ac0dbcee78b9253 sha1(uuid()): 1ee53f3c9a04c152c456e8361409bb2452013f4c sha2(uuid(),224): d8a2caa30e232dac5f6d03ab5e7b3912383e6747ad31e43f57b4e730 sha2(uuid(),256): 1e367343a0284aa60379cdfe67aa6409931e7f8a6cf6d762772aac1d95707b96 sha2(uuid(),384): 4f6f8996116d4e7488ee924b5e1b1aec9b9df1dbaa9f543690f52b11b018826372c2ab1ca7f256c162915553b4e79c10 sha2(uuid(),512): 399ead2cbe1b6fbcc9a09d9315b5df8d441810967c77e308a755112784b1f6974ea83bb15d89b5fa87907c3f7efff9e6d0731e6cd35ef2ca88ecbda5f9e41155 1 row in set (0.00 sec) ERROR: No query specified
mysql> select uuid(),md5(uuid()),sha(uuid()),sha1(uuid()),sha2(uuid(),224),sha2(uuid(),256),sha2(uuid(),384),sha2(uuid(),512)\G; *************************** 1. row *************************** uuid(): 38d86b70-8f14-11ee-acec-080027b9fde5 md5(uuid()): 54a8fcc367ad3e93a72e64f259be39b7 sha(uuid()): 03c29d9a53d2ebc017a1fc99b3fc84936bf44487 sha1(uuid()): 52ca5f593b890a268250d309b1cf8bf6fd0cf80e sha2(uuid(),224): 4c77b8b2f973a69faf6b83261cabdc35a164821648f888c8cc993db7 sha2(uuid(),256): 9cff271c04127b27ea412338a091096cd23e078e15aa8358794dae9d74e40e8b sha2(uuid(),384): 763a63b607463a574646ea0cb47e1919f5dcf4483ce618c6a76ddafd7bc28754b043ed5148d09e1559457d648981535d sha2(uuid(),512): 58821647f8353300cb257cf413c18ab741077ec6b245ff82819b5275736a91ea935057047b00ea500f6d7d96516269bf285a0ce9932ddd91b451e2042ce9f39e 1 row in set (0.00 sec) ERROR: No query specified mysql> select now(),md5(now()),sha(now()),sha1(now()),sha2(now(),224),sha2(now(),256),sha2(now(),384),sha2(now(),512)\G; *************************** 1. row *************************** now(): 2023-11-30 08:06:12 md5(now()): bd276e21acdeb0f85f9e8d3d8ab24fd9 sha(now()): 14612b06b686a672a17f1f936ca2c72944edd0d0 sha1(now()): 14612b06b686a672a17f1f936ca2c72944edd0d0 sha2(now(),224): e20c5c9a11ce1ca99c65baaea650ca19ca2ba067525cbcabbe89b26d sha2(now(),256): 3bd5d3f93e3e6f783467be66f504eaa412771feb8e80a3bf8e4bea394ecd7b1f sha2(now(),384): 72814348732b7eca4d51c38f1f40642c419fb6ef21f6ef515f52b22d0a45c7f7551948cc29ada2d0d046d739e798d9f9 sha2(now(),512): a521b96ecee8db759b5b2e71f1eee55e06be2e305b47b6bf73bac8305142ad672ab9de1f66d0a7efede4027551fc3c8bc12c29b664ad2405bddcbff90f5502a1 1 row in set (0.00 sec) ERROR: No query specified mysql>
ysql> explain select * from t3 order by id limit 10\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t3 partitions: NULL type: index possible_keys: NULL key: PRIMARY key_len: 8 ref: NULL rows: 10 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) ERROR: No query specified
mysql> explain format=json select * from t3 order by id desc limit 100; +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost_info": { "query_cost": "176173.87" }, "ordering_operation": { "using_filesort": false, "table": { "table_name": "t3", "access_type": "index", "key": "PRIMARY", "used_key_parts": [ "id" ], "key_length": "8", "rows_examined_per_scan": 100, "rows_produced_per_join": 1000000, "filtered": "100.00", "backward_index_scan": true, "cost_info": { "read_cost": "76173.88", "eval_cost": "100000.00", "prefix_cost": "176173.88", "data_read_per_join": "1G" }, "used_columns": [ "id", "author", "comment", "content", "header", "isbn", "title", "topic", "summary" ] } } } } | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 1 warning (0.02 sec)
//mysql like multiple key words;
select * from t1 where col1 like '%key1%' or col1 like '%key2%'; select * from t1 where col1 regexp 'key1|key2';
//export select result into file,copy from https://www.databasestar.com/mysql-output-file/#:~:text=slow%20and%20manual.-,Save%20MySQL%20Results%20to%20a%20File,OUTFILE%20'%2Ftemp%2Fmyoutput.
show global variables like 'secure_file_priv'; +------------------+-----------------------+ | Variable_name | Value | +------------------+-----------------------+ | secure_file_priv | /var/lib/mysql-files/ | select id,name,author,create_time from t1 limit 10 into outfile '/var/lib/mysql-files/t1102.txt' fields terminated by ',' enclosed by '\'' lines terminated by '\n'; (select 'id','name','create_time') union all (select id,name,create_time from t1 limit 1000000) into outfile '/var/lib/mysql-files/t11003.txt' fields terminated by ',' enclosed by '\'' lines terminated by '\n';
//time precision to milliseconds now(3),microseconds now(6);
mysql> select now(),now()+0,now(3),now(3)+0,now(6),now(6)+0; +---------------------+----------------+-------------------------+--------------------+----------------------------+-----------------------+ | now() | now()+0 | now(3) | now(3)+0 | now(6) | now(6)+0 | +---------------------+----------------+-------------------------+--------------------+----------------------------+-----------------------+ | 2023-12-02 20:57:34 | 20231202205734 | 2023-12-02 20:57:34.827 | 20231202205734.827 | 2023-12-02 20:57:34.827852 | 20231202205734.827852 | +---------------------+----------------+-------------------------+--------------------+----------------------------+-----------------------+ 1 row in set (0.00 sec)
//mysql uuid remove horizontal lines mysql> select uuid(),replace(uuid(),"-",""); +--------------------------------------+----------------------------------+ | uuid() | replace(uuid(),"-","") | +--------------------------------------+----------------------------------+ | a65e1504-911a-11ee-b138-546ceb385bb2 | a65e1516911a11eeb138546ceb385bb2 | +--------------------------------------+----------------------------------+ 1 row in set (0.00 sec)
mysql> select now(),uuid(),concat(now()+0,replace(uuid(),"-","")); +---------------------+--------------------------------------+------------------------------------------------+ | now() | uuid() | concat(now()+0,replace(uuid(),"-","")) | +---------------------+--------------------------------------+------------------------------------------------+ | 2023-12-02 21:58:11 | d446c00f-911a-11ee-b138-546ceb385bb2 | 20231202215811d446c066911a11eeb138546ceb385bb2 | +---------------------+--------------------------------------+------------------------------------------------+ 1 row in set (0.01 sec)
copy some column fields from another table only pay attention to the columns types regradless of the whole table structure
insert into t2(id,topic,create_time) select id,topic,create_time from t1 limit 10; mysql> select * from t2; +----+--------+---------+---------+--------+------+--------+--------+--------+---------+-------+--------------------------------------+---------------------+---------------------+------+ | id | author | comment | content | header | isbn | memory | object | result | summary | title | topic | create_time | update_time | name | +----+--------+---------+---------+--------+------+--------+--------+--------+---------+-------+--------------------------------------+---------------------+---------------------+------+ | 1 | | | | | | | | | | | d4506084-525d-4600-a8f4-5d1cc32b663a | 2023-11-26 19:24:11 | 2023-12-06 07:46:27 | | | 2 | | | | | | | | | | | 35945b20-b51b-4373-9104-0e6fe468f641 | 2023-11-26 19:24:11 | 2023-12-06 07:46:27 | | | 3 | | | | | | | | | | | c830428f-d5b4-46ef-99c9-75e8c3a0632c | 2023-11-26 19:24:11 | 2023-12-06 07:46:27 | | | 4 | | | | | | | | | | | 2c602308-d2a7-4d2d-94d8-961d7def2512 | 2023-11-26 19:24:11 | 2023-12-06 07:46:27 | | | 5 | | | | | | | | | | | 1305db37-8621-424e-b0ea-7c12527252b7 | 2023-11-26 19:24:11 | 2023-12-06 07:46:27 | | | 6 | | | | | | | | | | | 7ef48df5-8197-41a5-817c-bfca160e17ca | 2023-11-26 19:24:11 | 2023-12-06 07:46:27 | | | 7 | | | | | | | | | | | 9db34c9d-ae7b-44a0-875f-9bd43859bfa8 | 2023-11-26 19:24:11 | 2023-12-06 07:46:27 | | | 8 | | | | | | | | | | | ff3f5f67-8b73-4f79-a2dd-90e0e4ff8c73 | 2023-11-26 19:24:11 | 2023-12-06 07:46:27 | | | 9 | | | | | | | | | | | 441b13b7-5c57-4f71-b206-345f1282b9be | 2023-11-26 19:24:11 | 2023-12-06 07:46:27 | | | 10 | | | | | | | | | | | 724a158c-7d2c-48bc-a145-877839a3726c | 2023-11-26 19:24:11 | 2023-12-06 07:46:27 | | +----+--------+---------+---------+--------+------+--------+--------+--------+---------+-------+--------------------------------------+---------------------+---------------------+------+ 10 rows in set (0.00 sec)
mysql> desc t1; +-------------+-----------------+------+-----+-------------------+-----------------------------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-----------------+------+-----+-------------------+-----------------------------------------------+ | id | bigint unsigned | NO | PRI | NULL | auto_increment | | author | varchar(40) | NO | | | | | comment | varchar(40) | NO | | | | | content | varchar(40) | NO | | | | | header | varchar(40) | NO | | | | | isbn | varchar(40) | NO | | | | | memory | varchar(40) | NO | | | | | object | varchar(40) | NO | | | | | result | varchar(40) | NO | | | | | summary | varchar(40) | NO | | | | | title | varchar(40) | NO | | | | | topic | varchar(40) | NO | | | | | create_time | datetime | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED | | update_time | datetime | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | | name | varchar(40) | NO | | | | +-------------+-----------------+------+-----+-------------------+-----------------------------------------------+ 15 rows in set (0.01 sec) mysql> desc t3; +-------------+-----------------+------+-----+-------------------+-------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-----------------+------+-----+-------------------+-------------------+ | id | bigint unsigned | NO | PRI | NULL | auto_increment | | topic | varchar(40) | NO | | | | | create_time | datetime | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED | +-------------+-----------------+------+-----+-------------------+-------------------+ 3 rows in set (0.00 sec) mysql> mysql> insert into t3(id,create_time) select id,create_time from t1 limit 10; Query OK, 10 rows affected (0.01 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> select * from t3; +----+-------+---------------------+ | id | topic | create_time | +----+-------+---------------------+ | 1 | | 2023-11-26 19:24:11 | | 2 | | 2023-11-26 19:24:11 | | 3 | | 2023-11-26 19:24:11 | | 4 | | 2023-11-26 19:24:11 | | 5 | | 2023-11-26 19:24:11 | | 6 | | 2023-11-26 19:24:11 | | 7 | | 2023-11-26 19:24:11 | | 8 | | 2023-11-26 19:24:11 | | 9 | | 2023-11-26 19:24:11 | | 10 | | 2023-11-26 19:24:11 | +----+-------+---------------------+ 10 rows in set (0.00 sec)
mysql create procedure for loops with parameters
drop procedure if exists insert_into_t2_sp; DELIMITER // CREATE PROCEDURE insert_into_t2_sp() BEGIN DECLARE i INT DEFAULT 2; WHILE (i <= 1000000) DO INSERT INTO `t2` (id) values (i); SET i = i+1; END WHILE; END; // CALL insert_into_t2_sp(); drop procedure if exists insert_into_t3_sp; DELIMITER // CREATE PROCEDURE insert_into_t3_sp (IN id_value bigint unsigned) BEGIN while(id_value<1000) do insert into t3(id) values(id_value); set id_value=id_value+1; end while; END; // DELIMITER ; call insert_into_t3_sp(1);
mysqldump -u username_value -pPassword_value db_value table_value >dumpfile.sql;
mysql 5.7.16 installer download url for once click installation
https://cdn.mysql.com/archives/mysql-installer/mysql-installer-community-5.7.16.0.msi
create table
drop database if exists mydb; CREATE DATABASE mydb DEFAULT CHARACTER SET utf8mb4; use mydb; drop table if exists t1; create table t1(id bigint not null auto_increment primary key,Author varchar(40) not null default '',Content Text not null,ISBN varchar(100) not null default '',Name varchar(300) not null default '',Summary Text not null ,Title varchar(40) not null default '',Topic varchar(300) not null default '',BookEnumKind varchar(300) not null default '',BookImg Text not null,CreateTime datetime not null default current_timestamp comment 'created time',UpdateTime datetime not null default current_timestamp comment 'Updated Time') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE='utf8mb4_general_ci';
show create table mydb.t1 CREATE TABLE `t1` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `Author` varchar(40) NOT NULL DEFAULT '''', `Content` text NOT NULL, `ISBN` varchar(100) NOT NULL DEFAULT '''', `Name` varchar(300) NOT NULL DEFAULT '''', `Summary` text NOT NULL, `Title` varchar(40) NOT NULL DEFAULT '''', `Topic` varchar(300) NOT NULL DEFAULT '''', `BookEnumKind` varchar(300) NOT NULL DEFAULT '''', `BookImg` text NOT NULL, `CreateTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ''created time'', `UpdateTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ''Updated Time'', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
//convert blob to string SELECT convert(congest using utf8) from mt where id=1;
MySql.Data.MySqlClient.MySqlException: 'Packets larger than max_allowed_packet are not allowed
1.cd C:\Program Files\MySQL\MySQL Server 9.0\bin; 2.mysql -u root -p enter password as prompted; 3.set global max_allowed_packet=1000000000; select @@max_allowed_packet;
浙公网安备 33010602011771号