How to check mysql version number
How to view the mysql version number: You can directly execute the [mysql -V] command on the command line to view the version number. You can also use the [mysql –help | grep Ver] command to view version-related information. mysql -u root -p select version(); 4. View the version information in status in the mysql command number mysql -u root -p status;
MySQL execution plan explain and index data structure deduction
mysql tutorialThe column introduces the execution plan explain and index data structure Preparation First build the database table, the MySQL table for demonstration, and the table creation statement: CREATE TABLE `emp` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key', `empno` int(11) DEFAULT NULL COMMENT ' ;employee number’, `ename` varchar(255) DEFAULT NULL COMMENT ’employee’s name’, `job` varchar(255) DEFAULT NULL COMMENT ‘job’, `mgr` varchar(255) DEFAULT NULL COMMENT & # 39; manager’s job number & # 39;, `hiredate` date DEFAULT NULL COMMENT & # 39; hire date & # 39;, `sal` double DEFAULT NULL COMMENT ' 39; Salary & # 39;, `comm` double DEFAULT NULL COMMENT & # 39; Allowance & # 39;, `deptno` int(11) DEFAULT NULL COMMENT & # 39; department number & # 39;, PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='employee table';CREATE TABLE `dept` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key', `deptno `int(11) DEFAULT NULL COMMENT 'department number', `dname` varchar(255) DEFAULT NULL COMMENT 'department name', `loc` varchar(255) DEFAULT NULL COMMENT ‘Address’, PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Department table';CREATE TABLE `salgrade` ( `id` int(11) NOT NULL COMMENT 'primary key', `grade` varchar(255) DEFAULT NULL COMMENT 'grade', `lowsal` varchar(255) DEFAULT NULL COMMENT 'minimum wage',…
Mysql three of relational database: start from the life cycle of a sql
The mysql tutorial column introduces the life cycle of SQL in relational databases. Connector: Establish a connection with MySQL for querying SQL statements and judging permissions. Query cache: If the statement is not in the query cache, it will continue to the subsequent execution phase. After the execution is completed, the execution result will be stored in the query cache If the query hits the cache, MySQL can directly return the result without performing the following complex operations, improving efficiency Analyzer: Perform hard parsing of SQL statements, and the analyzer will first perform lexical analysis. Analyze the components of an SQL statement. Determine whether the input SQL statement satisfies the grammatical rules. Optimizer: The optimizer decides which index to use when there are multiple indexes in the table; or when there are multiple indexes in a statement When tables are joined (join), the connection order of each table is determined. The logical results of different execution methods are the same, but the execution efficiency will be different, and the role of the optimizer is to decide which solution to use. Executor: With index: the first call is to fetch the first line that meets the conditions, and then loop This…
How does mysqldate insert null
Mysql date insert null method: first connect to the MySQL server; then use the use keyword to complete the database selection operation; finally insert a NULL into the date data type, the code is [insert into user (time) values ( null)]. 3. Create a simple table, where the date time format supports NULL assignment, example: create table user( id int(10) auto_increment primary key, time date); The above is the detailed content of how to insert null in mysql date, please pay attention to other related articles on 1024programmer.com for more information!
What should I do if mysql cannot successfully start the service
The solution to the failure of mysql to start the service successfully: first comment out the address mapped by localhost; then enter the bin directory of mysql to initialize mysql; finally execute the net start mysql command again to start the service. Note: The data folder after the datadir attribute must not be manually created by yourself. If I try to create it manually, there will be problems. If you create it yourself, you can delete it. Then perform the following operations. 3. In versions above mysql5.7, there is no data directory by default, that is, there is no initialization service. You need to initialize mysql before you can start the service, otherwise it will report “the service did not report any errors” and the startup will fail. Solution: Enter the bin directory of mysql; Execute mysqld –initialize-insecure, the first time you execute it, it will take longer, and there will be no output information after the execution. Check the bin directory at the same level and there will be a data folder with a bunch of files inside . 4. Execute the net start mysql command again to start the service, and found that the startup is successful! More…
It’s all here! ! The principle and precautions of MySQL master-slave replication
Mysql TutorialThe column introduces the principle and precautions of master-slave replication Precautions (1) The operating system versions and digits of the master and slave servers are consistent; (2) The master and slave databases The versions must be consistent; (3) The data in the Master and Slave databases must be consistent; (4) The Master opens the binary log, and the server_id of the Master and Slave must be unique within the LAN; Configure master-slave replication steps Master database (1) Installation Database; (2) Modify the database configuration file, specify the server_id, open the binary log (log-bin); (3) Start the database, check which log is currently, position number How much is it; (4) Log in to the database and authorize the data replication user (the IP address is the IP address of the slave machine. If it is a two-way master-slave, here you also need to authorize the IP address of the machine. The IP address is the slave IP address); (5) Backup the database (remember to lock and unlock); (6) Send the backup data to the Slave; p>(7) Start the database; The above steps are successfully built for one-way master-slave, and the steps required to build a two-way master-slave: (1) Log in…
Understanding MySQL physical files
mysql tutorial column introduces MySQL physical files. 1. The data storage file of the database The MySQL database will create a database named under the data directory folder, used to store table file data in the database. Different database engines have different extensions for each table, for example: MyISAM uses “.MYD” as the extension, Innodb uses “.ibd”, Archive uses “.arc”, and CSV uses “.csv”. 1. “.FRM” file Before 8.0, no matter what kind of storage engine, after creating a table, one will be generated to indicate the name ‘.frm’ files. The frm file mainly stores the data information related to the table, mainly including the definition information of the table structure. When the database crashes, the user can restore the data table structure through the frm file. 2. “.MYD” file The “.MYD” file is dedicated to the MyISAM storage engine and stores the data of the MyISAM table. Each MyISAM table will have a “.MYD” file corresponding to it, which is also stored in the folder of the database to which it belongs, together with the “.frm” file. 3. “.MYI” file The “.MYI” file is also dedicated to the MyISAM storage engine, mainly storing the index related to the MyISAM…
How does mysql prevent sql injection problems
Mysql prevents sql injection method: 1. Turn on the magic mode of php, set [magic_quotes_gpc = on]; 2. Write the code to filter sql special characters in the website code, and convert some special characters; 3. , Open the website firewall, IIS firewall, built-in parameters for filtering sql injection. How to prevent mysql from sql injection: 1. Turn on the magic mode of php and set magic_quotes_gpc = on, when some special characters appear on the front end of the website, they will be converted automatically, and converted into some other symbols, which will cause the sql statement to fail to execute. You can also find a professional website security company to handle it. The domestic SINE Security, NSFOCUS, and Venustech are all relatively good. They carry out security reinforcement for the website, server security protection, and mysql security deployment. 2. In the website code, write the code to filter special characters in sql, and convert some special characters, such as single quotes, commas, *, (brackets) AND 1=1, backslash, select union The sql statements such as queries are all filtered for security, restricting the input of these characters, and prohibiting them from being submitted to the backend. 3. Open the…
What is the mysql escape character
The mysql escape character is 【`】, which is used to avoid the column name or table name and the keyword conflict of mysql itself. It is usually used to indicate that the content is the database name, table name, field name, not a keyword . ` is the escape character of MySQL, which is used to avoid the conflict between the column name or table name and the keyword of mysql itself. All databases have similar settings, but mysql uses `. It is usually used to indicate that the contents are database names, table names, and field names, not keywords. For example: select * from table_name where `key` = 'key_name'; Among them, the key is mysql keyword, if the column name is a key, it needs to be escaped with “. Extension: \ needs to be used in the string to escape `, otherwise an error will be reported indicating a syntax error. mysql -uUser -pPasswd -e “select * from table_name where \`key\` = 'name';” More related free learning recommendations: mysql tutorial (video) The above is the detailed content of mysql escape characters, please pay attention to 1024programmer.com for more related articles!
What should be paid attention to in query optimization of big data in mysql
Notes for big data query optimization in mysql: 1. To optimize the query, try to avoid full table scanning; 2. Try to avoid judging the null value of the field in the where clause; 3. in and Not in should also be used with caution; 4. Try to avoid using or to connect in the where clause; 5. Try to avoid using cursors. The method of big data query optimization in mysql: 1. To optimize the query, you should try your best To avoid full table scans, first consider building indexes on the columns involved in where and order by. 2. You should try to avoid judging the null value of the field in the where clause, otherwise it will cause the engine to give up using the index and perform a full table scan, such as: select id from t where num is null can be in num Set the default value to 0, make sure there is no null value in the num column in the table, and then query like this: select id from t where num=0 3. Try to avoid using the != or operator in the where clause, otherwise the engine will give up using…
